PostgreSQL : DateTime functions and operators

Last update on November 13 2023 09:57:50 (UTC/GMT +8 hours)

DateTime functions and operators

There are various DateTime functions as well as operators available in PostgreSQL. All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone.

Date/Time Operators

Operator	Example	Result
+	date '2001-09-28' + integer '7'	date '2001-10-05'
+	date '2001-09-28' + interval '1 hour'	timestamp '2001-09-28 01:00:00'
+	date '2001-09-28' + time '03:00'	timestamp '2001-09-28 03:00:00'
+	interval '1 day' + interval '1 hour'	interval '1 day 01:00:00'
+	timestamp '2001-09-28 01:00' + interval '23 hours'	timestamp '2001-09-29 00:00:00'
+	time '01:00' + interval '3 hours'	time '04:00:00'
-	- interval '23 hours'	interval '-23:00:00'
-	date '2001-10-01' - date '2001-09-28'	integer '3' (days)
-	date '2001-10-01' - integer '7'	date '2001-09-24'
-	date '2001-09-28' - interval '1 hour'	timestamp '2001-09-27 23:00:00'
-	time '05:00' - time '03:00'	interval '02:00:00'
-	time '05:00' - interval '2 hours'	time '03:00:00'
-	timestamp '2001-09-28 23:00' - interval '23 hours'	timestamp '2001-09-28 00:00:00'
-	interval '1 day' - interval '1 hour'	interval '1 day -01:00:00'
-	timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'	interval '1 day 15:00:00'
*	900 * interval '1 second'	interval '00:15:00'
*	21 * interval '1 day'	interval '21 days'
*	double precision '3.5' * interval '1 hour'	interval '03:30:00'
/	interval '1 hour' / double precision '1.5'	interval '00:40:00'

DateTime functions

age(timestamp, timestamp)

The age() function subtract arguments, producing a "symbolic" result that uses years and months.

Return Type: interval

Example:

postgres=# SELECT age(timestamp '2003-05-15', timestamp '1973-07-19');
           age
-------------------------
 29 years 9 mons 27 days
(1 row)

age(timestamp)

The age() function is used to subtract age from current_date (at midnight).

Return Type : interval

Example:

postgres=# SELECT age(timestamp '1973-07-19');
          age
-----------------------
 41 years 1 mon 4 days
(1 row)

clock_timestamp()

The clock_timestamp() function shows current date and time (changes during statement execution).

Return Type: timestamp with time zone.

Example:

postgres=# SELECT clock_timestamp();
        clock_timestamp
-------------------------------
 2014-08-23 15:53:12.021+05:30
(1 row)

N.B. - The outputs depending upon the current date and time.

current_date()

Return Type: date

Example:

postgres=# SELECT current_date;
    date
------------
 2014-08-23
(1 row)

N.B. - The outputs depending upon the current date.

current_time()

Return Type: time with time zone

Example:

postgres=# SELECT current_time;
       timetz
--------------------
 15:57:42.588+05:30
(1 row)

N.B. - The outputs depending upon the current time.

current_timestamp

Return Type: timestamp with time zone

Example:

postgres=# SELECT current_timestamp;
              now
-------------------------------
 2014-08-23 15:58:45.345+05:30
(1 row)

N.B. - The outputs depending upon the current date and time.

date_part(text, timestamp)

The date_part() function is used to get subfield (equivalent to extract).

Return Type: double precision

Example:

postgres=# SELECT date_part('hour', timestamp '2002-09-17 19:27:45');
 date_part
-----------
        19
(1 row)

date_part(text, interval)

The date_part() function is used to get subfield (equivalent to extract).

Return Type: double precision

Example:

postgres=# SELECT date_part('month', interval '3 years 7 months');
 date_part
-----------
         7
(1 row)

date_trunc(text, timestamp)

The date_trunc() function is used to truncate to specified precision;

Return Type: timestamp

Example:

postgres=# SELECT date_trunc('hour', timestamp '2002-09-17 19:27:45');
     date_trunc
---------------------
 2002-09-17 19:00:00
(1 row)

extract(field from timestamp)

The date_trunc() function is used to get subfield.

Return Type : double precision

Example:

postgres=# SELECT extract(hour from timestamp '2002-09-17 19:27:45');
 date_part
-----------
        19
(1 row)

extract(field from interval)

The date_trunc() function is used to get subfield.

Return Type: double precision

Example:

postgres=# SELECT extract(month from interval '3 years 7 months');
 date_part
-----------
         7
(1 row)

isfinite(date)

The isfinite() function is used to get test for finite date (not +/-infinity).

Return Type: boolean

Example:

postgres=# SELECT isfinite(date '2002-09-17');
 isfinite
----------
 t
(1 row)

isfinite(timestamp)

The isfinite() function is used to get test for finite date (not +/-infinity).

Return Type: boolean

Example:

postgres=# SELECT isfinite(timestamp '2002-09-17 19:27:45');
 isfinite
----------
 t
(1 row)

isfinite(interval)

This function is used to test for finite interval.

Return Type: boolean

Example:

postgres=# SELECT isfinite(interval '7 hours');
 isfinite
----------
 t
(1 row)

justify_days(interval)

This function is used to adjust interval so 30-day time periods are represented as months

Return Type: interval

Example:

postgres=# SELECT justify_days(interval '47 days');
 justify_days
---------------
 1 mon 17 days
(1 row)

justify_hours(interval)

This function is used to adjust interval so 24-hour time periods are represented as days

Return Type: interval

Example:

postgres=# SELECT justify_hours(interval '32 hours');
 justify_hours
----------------
 1 day 08:00:00
(1 row)

justify_interval(interval)

This function is used to adjust interval using justify_days and justify_hours, with additional sign adjustments.

Return Type: interval

Example:

postgres=# SELECT justify_interval(interval '1 mon -1 hour');
 justify_interval
------------------
 29 days 23:00:00
(1 row)

localtime

This function is used to get current time of day.

Return Type: time

Example:

postgres=# SELECT localtime;
     time
--------------
 16:20:44.502
(1 row)

N.B. - The outputs depending upon the current time.

localtimestamp

This function is used to get current date and time (start of current transaction).

Return Type: timestamp

Example:

postgres=# SELECT localtimestamp;
        timestamp
-------------------------
 2014-08-23 16:21:17.182
(1 row)

N.B. - The outputs depending upon the current date and time.

now()

This function is used to get current date and time (start of current transaction).

Return Type: timestamp with time zone

Example:

postgres=# SELECT now();
              now
-------------------------------
 2014-08-23 16:21:55.715+05:30
(1 row)

N.B. - The outputs depending upon the current date and time.

statement_timestamp()

This function is used to get current date and time (start of current transaction).

Return Type: timestamp with time zone

Example:

postgres=# SELECT statement_timestamp();
      statement_timestamp
-------------------------------
 2014-08-23 16:22:43.525+05:30
(1 row)

N.B. - The outputs depending upon the current date.

timeofday()

This function is used to get current date and time (like clock_timestamp, but as a text string).

Return Type: text

Example:

postgres=# SELECT timeofday();
              timeofday
-------------------------------------
 Sat Aug 23 16:23:25.427000 2014 IST
(1 row)

N.B. - The outputs depending upon the current date.

transaction_timestamp()

This function is used to get current date and time (start of current transaction)

Return Type: timestamp with time zone

Example:

postgres=# SELECT transaction_timestamp();
     transaction_timestamp
-------------------------------
 2014-08-23 16:24:06.382+05:30
(1 row)

N.B. - The outputs depending upon the current date.