Contents
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
- returns
- handling of infinite datetimes improved (commit 647d87c5):
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
References
- PostgreSQL documentation: Date/Time Functions
- PostgreSQL documentation: EXTRACT, date_part