Product Documentation

Appearance

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 Dateparts.

For example, 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)

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)

Parent topic:Datetime Functions