- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

7.9 Date/Time Functions and Operators

Table 7-27 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 7-26 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to section 7.8 Data Type Formatting Functions. You should be familiar with the background information on date/time data types from section 6.5 Date/Time Types.

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. For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.

Table 7-26: Date/Time Operators
Operator Example
+ 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'
Table 7-27: Date/Time Functions
Function Return Type Description
age(timestamp, timestamp) interval Subtract arguments, producing a “symbolic” result that uses years and months
e.g. age(timestamp '2001-04-10', timestamp '1957-06-13') => 43 years 9 mons 27 days
age(timestamp) interval Subtract from current_date (at midnight)
e.g. age(timestamp '1957-06-13') => 43 years 8 mons 3 days
clock_timestamp() timestamp with time zone Current date and time (changes during statement execution); see section 7.9.4 Current Date/Time
current_date date Current date; see section 7.9.4 Current Date/Time
current_time time with time zone Current time of day; see section 7.9.4 Current Date/Time
current_timestamp timestamp with time zone Current date and time (start of current transaction); see section 7.9.4 Current Date/Time
date_part(text, timestamp) double precision Get subfield (equivalent to extract); see section 7.9.1 EXTRACT, date_part
e.g. date_part('hour', timestamp '2001-02-16 20:38:40') => 20
date_part(text, interval) double precision Get subfield (equivalent to extract); see section 7.9.1 EXTRACT, date_part
e.g. date_part('month', interval '2 years 3 months') => 3
date_trunc(text, timestamp) timestamp Truncate to specified precision; see also date_trunc
e.g. date_trunc('hour', timestamp '2001-02-16 20:38:40') => 2001-02-16 20:00:00
extract(field from timestamp) double precision Get subfield; see section 7.9.1 EXTRACT, date_part
e.g. extract(hour from timestamp '2001-02-16 20:38:40') => 20
extract(field from interval) double precision Get subfield; see section 7.9.1 EXTRACT, date_part
e.g. extract(month from interval '2 years 3 months') => 3
isfinite(date) boolean Test for finite date (not +/-infinity)
e.g. isfinite(date '2001-02-16') => true
isfinite(timestamp) boolean Test for finite time stamp (not +/-infinity)
e.g. isfinite(timestamp '2001-02-16 21:28:30') => true
isfinite(interval) boolean Test for finite interval
e.g. isfinite(interval '4 hours') => true
justify_days(interval) interval Adjust interval so 30-day time periods are represented as months
e.g. justify_days(interval '35 days') => 1 mon 5 days
justify_hours(interval) interval Adjust interval so 24-hour time periods are represented as days
e.g. justify_hours(interval '27 hours') => 1 day 03:00:00
justify_interval(interval) interval Adjust interval using justify_days and justify_hours, with additional sign adjustments
e.g. justify_interval(interval '1 mon -1 hour') => 29 days 23:00:00
localtime time Current time of day; see section 7.9.4 Current Date/Time
localtimestamp timestamp Current date and time (start of current transaction); see section 7.9.4 Current Date/Time
now() timestamp with time zone Current date and time (start of current transaction); see section 7.9.4 Current Date/Time
statement_timestamp() timestamp with time zone Current date and time (start of current statement); see section 7.9.4 Current Date/Time
timeofday() text Current date and time (like clock_timestamp, but as a text string); see section 7.9.4 Current Date/Time
transaction_timestamp() timestamp with time zone Current date and time (start of current transaction); see section 7.9.4 Current Date/Time

In addition to these functions, the SQL OVERLAPS operator is supported:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Result: true

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 '2005-04-02 12:00-07' + interval '1 day' will produce timestamp with time zone '2005-04-03 12:00-06', while adding interval '24 hours' to the same initial timestamp with time zone produces timestamp with time zone '2005-04-03 13:00-06', as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT.

Note there can be ambiguity in the months returned by age because different months have a different number of days. PostgreSQL's approach uses the month from the earlier of the two dates when calculating partial months. For example, age('2004-06-01', '2004-04-30') uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30.

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition