Product Documentation

Appearance

All platforms

EXTRACT ​

Return a specific date part (such as the month, day, or hour) from a date, timestamp, timestamp with time zone, or time expression, or from an INTERVAL literal.

EXTRACT( datepart FROM { date | timestamptz | timestamp | time | interval } )

See Supported Date Parts.

Examples ​

Extract the day from a timestamp column:

premdb=# SELECT matchday, EXTRACT(day FROM matchday) 
FROM match WHERE htid=26 AND seasonid=10 ORDER BY 1 LIMIT 5;
     matchday       | date_part 
---------------------+-----------
 2001-08-19 00:00:00 |        19
 2001-09-08 00:00:00 |         8
 2001-09-22 00:00:00 |        22
 2001-10-20 00:00:00 |        20
 2001-10-27 00:00:00 |        27
(5 rows)

The following example extracts the timezone date part from the matchday column (a timestamptz column in the matchtz table):

premdb=# select extract(timezone from matchday),* from matchtz order by seasonid desc limit 5;
 date_part | seasonid |        matchday        | htid | atid | ftscore | htscore 
-----------+----------+------------------------+------+------+---------+---------
   -28800 |       22 | 2013-11-09 00:00:00-08 |   14 |   93 | 2-2     | 1-0
   -28800 |       22 | 2014-01-29 00:00:00-08 |   14 |   94 | 0-0     | 0-0
   -25200 |       22 | 2014-04-12 00:00:00-07 |   16 |   52 | 1-0     | 0-0
   -25200 |       22 | 2013-10-26 00:00:00-07 |   16 |   51 | 0-2     | 0-0
   -28800 |       22 | 2013-12-07 00:00:00-08 |   16 |   61 | 2-0     | 1-0
(5 rows)

When you specify a literal expression, include the DATE, TIMESTAMP, TIMESTAMPTZ, TIME, or INTERVAL keyword. For example:

premdb=# SELECT EXTRACT(dow FROM TIMESTAMP '2016-04-17 12:00:00');
 date_part 
-----------
        0
(1 row)

premdb=# SELECT EXTRACT(hr FROM TIME '11:21:15');
 date_part 
-----------
       11
(1 row)