EXTRACT

​

Syntax

​

Calendar (scalar)

EXTRACT(unit FROM value)

• The unit must apply to the input type. Use one of the calendar or clock units listed above.

​

Arguments

​

Returns

• Type: INTEGER
• NULL behavior: NULL temporal input yields NULL. Unsupported unit/type combinations can raise errors.

​

Properties

​

Usage notes

• EXTRACT uses a unit keyword and FROM: EXTRACT(unit FROM value). DATE_PART and DATEPART use a STRING field and comma-separated arguments. Their reported return types can differ.
• With native execution, EXTRACT returns INTEGER. Java execution reports BIGINT and is outside this page’s scope.
• YEAR and MONTH can handle some out-of-range UTC or timezone-free dates that other fields cannot. Other components remain limited to their supported calendar range.

​

Examples

​

Extract a year

SELECT EXTRACT(YEAR FROM DATE '2024-01-01') AS "value";

​

Extract time fields

SELECT EXTRACT(HOUR FROM TIMESTAMP '2024-03-15 10:30:45') AS hour, EXTRACT(MINUTE FROM TIMESTAMP '2024-03-15 10:30:45') AS minute;

​

Limitations

• Use the listed units. DECADE, CENTURY, and MILLENNIUM are outside this page’s supported forms.
• EPOCH, DOW, and ISODOW have different numbering or return-type rules and are outside this form.
• The parser accepts additional time-frame names and units; this form does not imply support for every such name.

​

Related functions

• date_part
• days