date_part()

A function for retrieving elements of a date or timestamp

date_part() is a system function for retrieving elements of a date or timestamp, similar to extract().

date_part() was added in PostgreSQL 6.1.

Usage

date_part(field text, source date) → double precision
date_part(field text, source interval) → double precision
date_part(field text, source timestamp) → double precision
date_part(field text, source timestamp with time zone) → double precision

date_part() does essentially the same thing as the SQL-standard extract() function, and was originally intended to provide Ingres compatibility. From PostgreSQL 14 it is recommended to use extract(), as that function now returns numeric, whereas the double precision value returned by date_part() and extract() (prior to PostgreSQL 14) can result in a loss of precision in certain cases.

Change history

Examples

Usage example for date_part():

postgres=# SELECT date_part('year', now());
 date_part 
-----------
      2021
(1 row)

In PostgreSQL 14 and later, the value returned by date_part() may not be identical to the one returned by extract()]:

postgres=# WITH now AS (
  SELECT NOW() AS now
)
SELECT date_part('epoch', now.now), extract('epoch' FROM now.now)
  FROM now;
    date_part     |      extract      
------------------+-------------------
 1637913804.90921 | 1637913804.909210
(1 row)

Categories

Date and time, System function

See also

extract()