The SQL92 standard specifies a data typed called an interval , which represents a fixed span of time. By itself, an interval represents only a quantity of time , and does not begin or end at any set date or time. These intervals can be useful when applied to date and time values to calculate a new date or time, either by subtracting or adding the quantity. They can also be handy for quickly determining the precise interval between two date or time values. This can be achieved by subtracting date values, time values or timestamps from one another.

The two syntax variations in Table 3-23 can specify an interval within PostgreSQL:

Table 3-23. Interval Formats

Format

Description

qty unit [ ago ]

A single quantity and unit of time, with an optional ago quantity inversion term

[ qty1 unit1 , qty2 unit2 , ... ] [ ago ]

Several quantities, each with a discrete unit of time, with an optional ago quantity inversion term

qty

Specifies the quantity of your interval, which may be any whole integer, or floating point number in the case of microseconds. The literal meaning of this number is qualified by the subsequent unit .

unit

Qualifies the qty provided. The unit may be any one of the following keywords: second, minute, hour, day, week, month, year, decade, century, millennium. It can also be an abbreviation or plurals of the previously mentioned units.

ago

The optional ago term of the interval determines whether or not you are describing a period of time before or after an associated time. You can think of it as a negative sign for date and time types.

Example 3-28. Interpretting Interval Formats

booktown=# SELECT date('1980-06-25');
    date
------------
 1980-06-25
(1 row)

booktown=# SELECT interval('21 years 8 days');
    interval
-----------------
 21 years 8 days
(1 row)

booktown=# SELECT date('1980-06-25') + interval('21 years 8 days') 
booktown-# AS spanned_date;
      spanned_date
------------------------
 2001-07-03 00:00:00-07
(1 row)
 
booktown=# SELECT date('1980-06-25') - interval('21 years 8 days ago') 
booktown-# AS twice_inverted_interval_date;
 twice_inverted_interval_date
------------------------------
 2001-07-03 00:00:00-07
(1 row)

Example 3-28 shows functional syntax for date and interval values being meaningfully combined. You can see that subtracting an inverted time interval (e.g., one with the term ago ) is functionally identical to adding a normal interval. This can be thought of as similar to the effect of adding negative numbers to integer values.