EXTRACT

Purpose

This function extracts specific values from a timestamp, date or interval.

Syntax

extract::=

Usage Notes

  • Valid parameters for the different data types:

    • DATE YEAR, MONTH, DAY
    TIMESTAMP YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
    TIMESTAMP WITH LOCAL TIME ZONE YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
    INTERVAL YEAR TO MONTH YEAR, MONTH
    INTERVAL DAY TO SECOND DAY, HOUR, MINUTE, SECOND

  • When extracting seconds, the milliseconds contained in the timestamp or interval are also extracted.
  • For data type TIMESTAMP WITH LOCAL TIME ZONE, this function is calculated within the session time zone.

Example

SELECT EXTRACT(SECOND FROM TIMESTAMP '2000-10-01 12:22:59.123') EXS,
       EXTRACT(MONTH FROM DATE '2000-10-01') EXM,
       EXTRACT(HOUR FROM INTERVAL '1 23:59:30.123' DAY TO SECOND) EXH;
Result
EXS EXM EXH
59.123 10 23