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

PostgreSQL 14 and later:

extract ( field from timestamp ) → numeric
extract ( field from interval ) → numeric

PostgreSQL 13 and earlier:

extract ( field from timestamp ) → double precision
extract ( field from interval ) → double precision

Change history

  • PostgreSQL 14
  • 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

Examples

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

Categories

Date and time, System function

See also

date_part()