EXTRACT

Retrieves the designated portion of a date, timestamp, or interval.

See also: DATE_PART

Syntax

-- Extract from a date or timestamp
EXTRACT(
  YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND |
  DOW | DOY | EPOCH | ISODOW | YEARWEEK | MILLENNIUM
  FROM <date_or_timestamp>
)

-- Extract from an interval
EXTRACT( YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND | MICROSECOND ｜ EPOCH FROM <interval> )

Return Type

The return type depends on the field being extracted:

• Returns Integer: When extracting discrete date or time components (e.g., YEAR, MONTH, DAY, DOY, HOUR, MINUTE, SECOND), the function returns an Integer.

  SELECT EXTRACT(DAY FROM now());  -- Returns Integer
  SELECT EXTRACT(DOY FROM now());  -- Returns Integer
  

• Returns Float: When extracting EPOCH (the number of seconds since 1970-01-01 00:00:00 UTC), the function returns a Float, as it may include fractional seconds.

  SELECT EXTRACT(EPOCH FROM now());  -- Returns Float
  

Examples

This example extracts various fields from the current timestamp:

SELECT
  NOW(),
  EXTRACT(DAY FROM NOW()),
  EXTRACT(DOY FROM NOW()),
  EXTRACT(EPOCH FROM NOW()),
  EXTRACT(ISODOW FROM NOW()),
  EXTRACT(YEARWEEK FROM NOW()),
  EXTRACT(MILLENNIUM FROM NOW());

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│            now()           │ EXTRACT(DAY FROM now()) │ EXTRACT(DOY FROM now()) │ EXTRACT(EPOCH FROM now()) │ EXTRACT(ISODOW FROM now()) │ EXTRACT(YEARWEEK FROM now()) │ EXTRACT(MILLENNIUM FROM now()) │
├────────────────────────────┼─────────────────────────┼─────────────────────────┼───────────────────────────┼────────────────────────────┼──────────────────────────────┼────────────────────────────────┤
│ 2025-04-16 18:04:22.773888 │                      16 │                     106 │         1744826662.773888 │                          3 │                       202516 │                              3 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

This example extracts the number of days from an interval:

SELECT EXTRACT(DAY FROM '1 day 2 hours 3 minutes 4 seconds'::INTERVAL);

┌─────────────────────────────────────────────────────────────────┐
│ EXTRACT(DAY FROM '1 day 2 hours 3 minutes 4 seconds'::INTERVAL) │
├─────────────────────────────────────────────────────────────────┤
│                                                               1 │
└─────────────────────────────────────────────────────────────────┘