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.

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.