Date and time functions perform conversion, extraction, or manipulation operations on date and time data types and can return date and time information.
Functions that take
TIMESTAMP inputs come in two variants:
TIME WITH TIME ZONEor
TIMESTAMP WITH TIME ZONE
TIME WITHOUT TIME ZONEor
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
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
TIMESTAMP WITH TIME ZONE '2014-04-02 12:00-07' + INTERVAL '1 day'
TIMESTAMP WITH TIME ZONE '2014-04-03 12:00-06'
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
Date/Time Functions in Transactions
Certain date/time functions such as
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.