EXTRACT function

The EXTRACT function returns a date or time part from a TIMESTAMP, TIMESTAMPTZ, TIME, or TIMETZ value. Examples include a day, month, year, hour, minute, second, millisecond, or microsecond from a timestamp.

Syntax

EXTRACT(datepart FROM source)

Arguments

datepart

The subfield of a date or time to extract, such as a day, month, year, hour, minute, second, millisecond, or microsecond. For possible values, see Date parts for date or timestamp functions.

source

A column or expression that evaluates to a data type of TIMESTAMP, TIMESTAMPTZ, TIME, or TIMETZ.

Return type

INTEGER if the source value evaluates to data type TIMESTAMP, TIME, or TIMETZ.

DOUBLE PRECISION if the source value evaluates to data type TIMESTAMPTZ.

Examples with TIMESTAMP

The following example determines the week numbers for sales in which the price paid was $10,000 or more. This example uses the TICKIT data. For more information, see Sample database

select salesid, extract(week from saletime) as weeknum
from sales 
where pricepaid > 9999 
order by 2;

salesid | weeknum
--------+---------
 159073 |       6
 160318 |       8
 161723 |      26

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

The following example returns the millisecond value from a literal timestamp value.

select extract(ms from timestamp '2009-09-09 12:08:43.101');
            
date_part
-----------
101

Examples with TIMESTAMPTZ

The following example returns the year value from a literal timestamptz value.

select extract(year from timestamptz '1.12.1997 07:37:16.00 PST');
            
date_part
-----------
1997

Examples with TIME

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 TIMETZ

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 timetz '18:25:33.123456 EST');
            
 date_part
-----------
     123

The following example returns the timezone offset hour from UTC from a literal timetz value.

select extract(timezone_hour from timetz '1.12.1997 07:37:16.00 PDT');
            
date_part
-----------
-7