EXTRACT¶

The EXTRACT function in SQL is used to retrieve a specific datetime field from a date, time, or interval.

Syntax¶

EXTRACT(field FROM source)

• field: The datetime field to extract. Valid fields include YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
• source: The date, time, or interval value from which the datetime field is to be extracted.

Usage¶

Here are examples of how EXTRACT can be used to pull specific datetime components from various types of datetime and interval data.

1. Extracting the HOUR from a TIMESTAMP:

   SELECT EXTRACT(HOUR FROM TIMESTAMP '2016-12-31 13:30:15') as extract;
   

   This returns 13.

2. Extracting the YEAR from a TIMESTAMP:

   SELECT EXTRACT(YEAR FROM TIMESTAMP '2016-12-31 13:30:15') as extract;
   

   This returns 2016.

3. Extracting the SECOND from a TIME value:

   SELECT EXTRACT(SECOND FROM TIME '17:12:28') as extract;
   

   This returns 28.

4. Extracting the DAY from a DATE value:

   SELECT EXTRACT(DAY FROM DATE '2021-10-06') as extract;
   

   This returns 6.

5. Extracting from INTERVAL data:

   SELECT EXTRACT(YEAR FROM INTERVAL '3' YEAR) as extract;
   SELECT EXTRACT(MINUTE FROM INTERVAL '7' MINUTE) as extract;
   

   These return 3 and 7, respectively.

Note that the EXTRACT function expects the source to be of a compatible datetime or interval type. Using a value of an incompatible type, such as a number or a string that cannot be interpreted as a datetime, will result in an error.