EXTRACT
Returns the given part of a date, time or interval.
PostgreSQL 17.9
EXTRACT(field FROM source)- field
- Part to get, a keyword without quotesKeywordCENTURYDAYDECADEDOWDOYEPOCHHOURISODOWISOYEARJULIANMICROSECONDSMILLENNIUMMILLISECONDSMINUTEMONTHQUARTERSECONDTIMEZONETIMEZONE_HOURTIMEZONE_MINUTEWEEKYEAR
- FROM source
- Date, time or intervaltimestamp | interval
Return value
A number (numeric), for example 3 for MONTH; with a fractional part for SECOND; EPOCH gives the seconds since 1970-01-01 00:00 UTC for a date and the length in seconds for an interval
Examples
Year from a date
PostgreSQL 17.9
SELECT EXTRACT(
YEAR
FROM TIMESTAMP '2023-01-01'
);Birth year from a table
PostgreSQL 17.9
SELECT member_name,
EXTRACT(
YEAR
FROM birthday
) AS birth_year
FROM FamilyMembers;Seconds in an interval
PostgreSQL 17.9
SELECT EXTRACT(
EPOCH
FROM INTERVAL '1 day 2 hours'
);DOW and ISODOW for a Sunday
PostgreSQL 17.9
SELECT EXTRACT(
DOW
FROM DATE '2024-03-10'
) AS dow,
EXTRACT(
ISODOW
FROM DATE '2024-03-10'
) AS isodow;Week and ISO year at the turn of the year
PostgreSQL 17.9
SELECT EXTRACT(
WEEK
FROM DATE '2021-01-01'
) AS week,
EXTRACT(
ISOYEAR
FROM DATE '2021-01-01'
) AS iso_year,
EXTRACT(
YEAR
FROM DATE '2021-01-01'
) AS year;Details
This is the SQL-standard syntax; DATE_PART does the same but returns double precision.
Day of the week: DOW gives 0 for Sunday and 6 for Saturday, while ISODOW goes from 1 for Monday to 7 for Sunday. MySQL DAYOFWEEK counts differently: Sunday is 1, Saturday is 7.
WEEK is the ISO week number: a week starts on Monday, and the first days of January can belong to the last week of the previous year. That is why the week number is used together with ISOYEAR, not YEAR.