Date/time functions

Date and time functions perform conversion, extraction, or manipulation operations on date and time data types and can return date and time information.

Date and time functions perform conversion, extraction, or manipulation operations on date and time data types and can return date and time information.

Usage

Functions that take TIME or TIMESTAMP inputs come in two variants:

  • TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE

  • TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE

For brevity, these variants are not shown separately.

The + and * operators come in commutative pairs; for example, both DATE + INTEGER and INTEGER + DATE. We show only one of each such pair.

Daylight savings time considerations

When adding an INTERVAL value to (or subtracting an INTERVAL value from) a TIMESTAMP WITH TIME ZONE value, the days component advances (or decrements) the date of the TIMESTAMP WITH TIME ZONE by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means INTERVAL '1 day' does not necessarily equal INTERVAL '24 hours'.

For example, with the session time zone set to CST7CDT:

TIMESTAMP WITH TIME ZONE '2014-04-02 12:00-07' + INTERVAL '1 day'

produces

TIMESTAMP WITH TIME ZONE '2014-04-03 12:00-06'

Adding INTERVAL '24 hours' to the same initial TIMESTAMP WITH TIME ZONE produces

TIMESTAMP WITH TIME ZONE '2014-04-03 13:00-06',

This result occurs because there is a change in daylight saving time at 2014-04-03 02:00 in time zone CST7CDT.

Date/time functions in transactions

Certain date/time functions such as CURRENT_TIMESTAMP and NOW return the start time of the current transaction; for the duration of that transaction, they return the same value. Other date/time functions such as TIMEOFDAY always return the current time.

See also

Template patterns for date/time formatting