extract()

A function for retrieving elements of a date or time value

extract() is a system function for retrieving elements of a date or time value.

extract() has always been present in PostgreSQL.

Usage

extract ( field from timestamp ) → numeric

extract ( field from interval ) → numeric

extract ( field from timestamp ) → double precision

extract ( field from interval ) → double precision

PostgreSQL 14
- return type changed from double precision to numeric (commit a2da77cd)
PostgreSQL 9.6
- handling of infinite datetimes improved (commit 647d87c5):
  - returns infinity or -infinity as appropriate when the requested field is one that is monotonically increasing, e.g. year, epoch
  - returns NULL in other cases
  - throws an ERROR if an invalid unit name is provided

Basic usage example for extract():

postgres=# SELECT extract('year' FROM now());
 extract 
---------
    2021
(1 row)

Attempting to extract an invalid time zone unit:

postgres=# SELECT extract('foo' FROM now());
ERROR:  timestamp with time zone units "foo" not recognized