EXTRACT function
The EXTRACT function returns a date or time part, such as a day, month, or year, hour, minute, second, millisecond, or microsecond from a TIMESTAMP value or expression, TIME, or TIMETZ.
Syntax
EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp | time | timetz } )
Arguments
- datepart
-
For possible values, see Date parts for date or timestamp functions.
- literal
-
A timestamp value, enclosed in single quotation marks and preceded by the TIMESTAMP keyword.
- timestamp | times | timestz
-
A TIMESTAMP, TIMESTAMPTZ, TIME, or TIMETZ column, or an expression that implicitly converts to a TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, or TIMETZ.
Return type
INTEGER if the argument is TIMESTAMP, TIME, or TIMETZ
DOUBLE PRECISION if the argument is TIMESTAMPTZ
Examples with a timestamp column
The following example determines the week numbers for sales in which the price paid was $10,000 or more.
select salesid, extract(week from saletime) as weeknum from sales where pricepaid > 9999 order by 2; salesid | weeknum --------+--------- 159073 | 6 160318 | 8 161723 | 26 (3 rows)
The following example returns the minute value from a literal timestamp value.
select extract(minute from timestamp '2009-09-09 12:08:43'); date_part ----------- 8 (1 row)
Examples with a time column
The following example table TIME_TEST has a column TIME_VAL (type TIME) with three values inserted.
select time_val from time_test; time_val --------------------- 20:00:00 00:00:00.5550 00:58:00
The following example extracts the minutes from each time_val.
select extract(minute from time_val) as minutes from time_test; minutes ----------- 0 0 58
The following example extracts the hours from each time_val.
select extract(hour from time_val) as hours from time_test; hours ----------- 20 0 0
The following example extracts milliseconds from a literal value.
select extract(ms from time '18:25:33.123456'); date_part ----------- 123
Examples with a TIMETZ column
The following example table TIMETZ_TEST has a column TIMETZ_VAL (type TIMETZ) with three values inserted.
select timetz_val from timetz_test; timetz_val ------------------ 04:00:00+00 00:00:00.5550+00 05:58:00+00
The following example extracts the hours from each timetz_val.
select extract(hour from timetz_val) as hours from time_test; hours ----------- 4 0 5
The following example extracts milliseconds from a literal value. Literals aren't converted to UTC before the extraction is processed.
select extract(ms from time '18:25:33.123456 EST'); date_part ----------- 123