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 seconds from each time_val.

select extract(minute from time_val) as secs from time_test;
            
secs
-----------
         0
         0
         58

The following example extracts the hours from each timetz_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(hour from time '18:25:33.123456 EST');
 date_part
-----------
     18