PostgreSQL EXTRACT() function

Last update on January 30 2025 06:04:48 (UTC/GMT +8 hours)

EXTRACT() function

The PostgreSQL EXTRACT() function is used to retrieves subfields such as year or hour from date/time values. The source must be a value expression of type timestamp, time, or interval. The field is an identifier or string that selects what field to be extracted from the source value.

The extract function is used to obtain specific subfields, such as year or hour, from date/time values. The source parameter should be a value expression that is of type timestamp, time, or interval. If the expression is of type date, it can be cast to a timestamp and used as well. The field parameter is an identifier or string that specifies which subfield to extract from the source value. The extract function returns numeric values.

Uses of EXTRACT() Function

Century: Extract the century from a timestamp.

Day: Extract the day from a timestamp or interval.

Month: Extract the month from a timestamp or interval.

Decade: Extract the decade from a timestamp.

Day of Week (DOW): Extract the day of the week from a timestamp.

Day of Year (DOY): Extract the day of the year from a timestamp.

Epoch: Extract the epoch value (number of seconds since January 1, 1970) from a timestamp or interval.

Hour: Extract the hour from a timestamp, time, or interval.

ISO Day of Week (ISODOW): Extract the ISO day of the week from a timestamp.

ISO Year (ISOYEAR): Extract the ISO year from a date or timestamp.

Julian Day: Extract the Julian day number from a date or timestamp.

Microseconds: Extract the number of microseconds from a time value.

Millennium: Extract the millennium from a timestamp.

Milliseconds: Extract the number of milliseconds from a time value.

Minute: Extract the minutes from a time value.

Quarter: Extract the quarter of the year from a timestamp.

Second: Extract the seconds from a timestamp or time value.

Week: Extract the week number of the year from a timestamp.

Year: Extract the year from a timestamp.

Syntax:

extract(field from timestamp)
or
extract(field from interval)

Return Type: double precision.

PostgreSQL Version: 15

Visual Presentation of PostgreSQL EXTRACT() function

EXTRACT() : century

To extract the century from a given date/time value, you can use the extract() function with the "century" field. The "century" field is an identifier or string that indicates the century subfield.

SQL Code:

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
     21|

EXTRACT() : day

To extract the day from a given date/time value, you can use the EXTRACT function with the "day" field.

SQL Code:

SELECT EXTRACT(DAY FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
     11|

To extract the number of days from an interval, you can use the EXTRACT function with the "day" field.

SELECT EXTRACT(DAY FROM INTERVAL '19 days 3 minute');

Output:

extract|
-------+
     19|

EXTRACT() : month

To extract the month from a given timestamp, you can use the EXTRACT function with the "month" field.

SQL Code:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
      3|

SELECT EXTRACT(MONTH FROM INTERVAL '3 years 5 months');

Output:

extract|
-------+
      5|

SELECT EXTRACT(MONTH FROM INTERVAL '3 years 15 months');

Output:

extract|
-------+
      3|

EXTRACT() : decade

To extract the decade from a given timestamp, you can use the EXTRACT function with the "decade" field. The year field divided by 10.

SQL Code:

SELECT EXTRACT(DECADE FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
    202|

For the given timestamp value '2023-03-11 17:43:17.436', the result is 202, indicating that it falls within the decade of the 2020s.

EXTRACT() : dow

To retrieve the day of the week (DOW) from a timestamp value the DOW can be used. It represents the day of the week as an integer, where Sunday is 0 and Saturday is 6.

SQL Code:

SELECT EXTRACT(DOW FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
      6|

This query returns the day of the week as a numeric value. For the given timestamp value '2023-03-11 17:43:17.436', the result is 6, indicating that it falls on a Satruday (since Sunday is represented by 0).

EXTRACT() : doy

To retrieve the day of the year (DOY) from a timestamp value the DOY is used. It represents the day of the year as an integer, ranging from 1 to 366 (or 365 in non-leap years).

SQL Code:

SELECT EXTRACT(DOY FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
     70|

This query returns the day of the year as a numeric value. For the given timestamp value '2023-03-11 17:43:17.436', the result will be 70, indicating that it is the 70th day of the year.

EXTRACT() : epoch

To retrieve the epoch value from a timestamp the EXTRACT function can be used. The epoch value represents the number of seconds that have elapsed since January 1, 1970 (Coordinated Universal Time - UTC).

SQL Code:

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2023-03-11 17:43:17.436-07');

Output:

extract          |
-----------------+
1678581797.436000|

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract          |
-----------------+
1678556597.436000|

SELECT EXTRACT(EPOCH FROM INTERVAL '3 days 10 hours');

Output:

extract      |
-------------+
295200.000000|

EXTRACT() : hour

To extract the hour from a given timestamp, time, or interval value, you can use the EXTRACT function with the "hour" field. The hour field is (0 - 23).

SQL Code:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
     17|

This query returns the hour as a numeric value. For the given timestamp value '2023-03-11 17:43:17.436', the result is 17, indicating the hour of the day.

SELECT EXTRACT(HOUR FROM interval '3 hours 10 minutes');

Output:

extract|
-------+
      3|

This query returns the hour 3 from the given interval value.

EXTRACT() : isodow

To extract the ISO day of the week (ISODOW) from a given timestamp, you can use the EXTRACT() function with the "isodow" field.

SQL Code:

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
      6|

For the given timestamp value '2023-03-11 17:43:17.436', the result IS 6, indicating that it falls on a Saturday. The ISO day of the week follows the ISO 8601 standard, where Monday is represented by 1 and Sunday by 7.

EXTRACT() : isoyear

To extract the ISO year (ISOYEAR) from a given date or timestamp, you can use the EXTRACT() function with the "isoyear" field.

The ISO year represents the year according to the ISO 8601 standard, which defines the start of a week as Monday and specifies the first week of the year as the week containing at least four days of that year.

SQL Code:

SELECT EXTRACT(ISOYEAR FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
   2023|

This query returns the ISO year for the given timestamp value '2023-03-11 17:43:17.436', based on the ISO week numbering system.

SELECT EXTRACT(ISOYEAR FROM DATE '2023-03-11');

Output:

extract|
-------+
   2023|

This query returns the ISO year as a numeric value. For the given date value '2023-03-11', the result is the ISO year it falls into, such as 2023.

EXTRACT() : julian

To extract the Julian day number from a given date or timestamp, you can use the EXTRACT() function with the "julian" field.

SQL Code:

SELECT EXTRACT(JULIAN FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract                     |
----------------------------+
2460015.73839625000000000000|

This query returns the Julian day number for the specified timestamp. The result will be a floating-point number representing the number of days since November 24, 4714 BC (noon Universal Time).

SELECT EXTRACT(JULIAN FROM DATE '2023-03-11');

Output:

extract|
-------+
2460015|

EXTRACT() : microseconds

To retrieve the number of microseconds from a given time value, you can use the EXTRACT() function with the "microseconds" field.

SQL Code:

SELECT EXTRACT(MICROSECONDS FROM TIME '17:43:17.436');

Output:

extract |
--------+
17436000|

EXTRACT() : millennium

To determine the millennium from a date, you can use the EXTRACT() function with the "millennium" field.

SQL Code:

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
      3|

Years in the 1900s are in the second millennium. The third millennium started January 1, 2001.

EXTRACT() : milliseconds

To extracts the milliseconds from a time value, you can use the EXTRACT() function with the "milliseconds" field.

SQL Code:

SELECT EXTRACT(MILLISECONDS FROM TIME '17:43:17.436');

Output:

extract  |
---------+
17436.000|

This query returnw the number of milliseconds from the time value '17:43:17.436'.

EXTRACT() : minute

To extracts the minutes from a time value, you can use the EXTRACT() function with the "minute" field.

SQL Code:

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
     43|

EXTRACT() : quarter

The EXTRACT() function with the QUARTER parameter returns the quarter of the year as an integer value. The quarter value will be in the range of 1 to 4, corresponding to the first, second, third, or fourth quarter of the year, respectively.

SQL Code:

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
      1|

This query returns the number of milliseconds from the time value '17:43:17.436'.

EXTRACT() : second

The EXTRACT() function with the SECOND parameter returns the second component of the timestamp as an integer value. The second value will be in the range of 0 to 59.

SQL Code:

SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract  |
---------+
17.436000|

SELECT EXTRACT(SECOND FROM TIME '17:43:17.436');

Output:

extract  |
---------+
17.436000|

EXTRACT() : week

The EXTRACT() function with the WEEK parameter returns the week number of the year as an integer value. The week number will be in the range of 1 to 53, depending on the specific date and the datestyle setting in PostgreSQL.
The ISO year represents the year according to the ISO 8601 standard, which defines the start of a week as Monday and specifies the first week of the year as the week containing at least four days of that year. If January 4th is in the previous year, then that week is considered as part of the last year's last week.

SQL Code:

SELECT EXTRACT(WEEK FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
     10|

The query returns the week number 10 because March 11th, 2023, falls in the 10th week of the year.

EXTRACT() : year

The EXTRACT() function with the YEAR parameter returnS the year as an integer value from the timestamp.

SQL Code:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-03-11 17:43:17.436');

Output:

extract|
-------+
   2023|

In this query the timestamp is '2023-03-11 17:43:17.436', the query returns the year 2023.

Previous: DATE_TRUNC function
Next: ISFINITE function