PostgreSQL - SQL Language Reference - Date/Time Functions and Operators

PostgreSQL Reference Manual - Volume 1 - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
ISBN 0954612027
RRP £32.00 ($49.95)

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

PostgreSQL Reference Manual
Volume 1

Buy the book here! >>>
support free software

7.9 Date/Time Functions and Operators

Table 7-26 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 7-25 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-25: 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'`
`-`	`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-26: 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` 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(timestamp)`	`boolean`	Test for finite time stamp (not equal to 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 '30 days')` => `1 month`
`justify_hours(interval)`	`interval`	Adjust interval so 24-hour time periods are represented as days e.g. `justify_hours(interval '24 hours')` => `1 day`
`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.

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

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.

PostgreSQL Reference Manual - Volume 1 - SQL Language Reference

See the print edition