PostgreSQL EXTRACT() function

Last update on July 22 2024 13:17:03 (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

Pictorial 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.

Code:

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

Sample 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.

Code:

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

Sample 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');

Sample Output:

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

EXTRACT() : month

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

Code:

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

Sample Output:

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

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

Sample Output:

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

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

Sample 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.

Code:

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

Sample 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.

Code:

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

Sample 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).

Code:

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

Sample 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).

Code:

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

Sample Output:

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

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

Sample Output:

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

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

Sample 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).

Code:

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

Sample 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');

Sample 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.

Code:

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

Sample 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.

Code:

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

Sample 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');

Sample 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.

Code:

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

Sample 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');

Sample 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.

Code:

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

Sample Output:

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

EXTRACT() : millennium

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

Code:

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

Sample 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.

Code:

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

Sample 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.

Code:

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

Sample 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.

Code:

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

Sample 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.

Code:

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

Sample Output:

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

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

Sample 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.

Code:

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

Sample 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.

Code:

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

Sample 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