INTERVALs represent periods of time that can be added to or subtracted from DATE, TIMESTAMP, TIMESTAMPTZ, or TIME values.
Name
Description
INTERVAL
Period of time
An INTERVAL can be constructed by providing amounts together with units.
Units that aren't months, days, or microseconds are converted to equivalent amounts in the next smaller of these three basis units.
SELECTINTERVAL1YEAR,-- single unit using YEAR keyword; stored as 12 monthsINTERVAL(random()*10)YEAR,-- parentheses necessary for variable amounts;-- stored as integer number of monthsINTERVAL'1 month 1 day',-- string type necessary for multiple units; stored as (1 month, 1 day)'16 months'::INTERVAL,-- string cast supported; stored as 16 months'48:00:00'::INTERVAL,-- HH::MM::SS string supported; stored as (48 * 60 * 60 * 1e6 microseconds);
Warning
Decimal values can be used in strings but are rounded to integers.
SELECTINTERVAL'1.5'YEARS;-- Returns 12 months; equivalent to `to_years(CAST(trunc(1.5) AS INTEGER))`
For more precision, use a more granular unit; e.g., 18 MONTHS instead of '1.5' YEARS.
Three basis units are necessary because a month does not correspond to a fixed amount of days (February has fewer days than March) and a day doesn't correspond to a fixed amount of microseconds.
The division into components makes the INTERVAL class suitable for adding or subtracting specific time units to a date. For example, we can generate a table with the first day of every month using the following SQL query:
When INTERVALs are deconstructed via the datepart function, the months component is additionally split into years and months, and the microseconds component is split into hours, minutes, and microseconds. The days component is not split into additional units. To demonstrate this, the following query generates an INTERVAL called period by summing random amounts of the three basis units. It then extracts the aforementioned six parts from period, adds them back together, and confirms that the result is always equal to the original period.
The microseconds component is split only into hours, minutes, and microseconds, rather than hours, minutes, seconds, and microseconds.
Additionally, the amounts of centuries, decades, quarters, seconds, and milliseconds in an INTERVAL, rounded down to the nearest integer, can be extracted via the datepart function. However, these components are not required to reassemble the original INTERVAL. In fact, if the previous query additionally extracted decades or seconds, then the sum of extracted parts would generally be larger than the original period since this would double count the months and microseconds components, respectively.
Note
All units use 0-based indexing, except for quarters, which use 1-based indexing.
INTERVALs can be added to and subtracted from TIMESTAMPs, TIMESTAMPTZs, DATEs, and TIMEs using the + and - operators.
SELECTDATE'2000-01-01'+INTERVAL1YEAR,TIMESTAMP'2000-01-01 01:33:30'-INTERVAL'1 month 13 hours',TIME'02:00:00'-INTERVAL'3 days 23 hours',-- wraps; equals TIME '03:00:00';
Note
Adding an INTERVAL to a DATE returns a TIMESTAMP even when the INTERVAL has no microseconds component. The result is the same as if the DATE was cast to a TIMESTAMP (which sets the time component to 00:00:00) before adding the INTERVAL.
Conversely, subtracting two TIMESTAMPs or two TIMESTAMPTZs from one another creates an INTERVAL describing the difference between the timestamps with only the days and microseconds components. For example:
SELECTTIMESTAMP'2000-02-06 12:00:00'-TIMESTAMP'2000-01-01 11:00:00',-- 36 days 1 hourTIMESTAMP'2000-02-01'+(TIMESTAMP'2000-02-01'-TIMESTAMP'2000-01-01'),-- '2000-03-03', NOT '2000-03-01';
Subtracting two DATEs from one another does not create an INTERVAL but rather returns the number of days between the given dates as integer value.
Warning
Extracting a component of the INTERVAL difference between two TIMESTAMPs is not equivalent to computing the number of partition boundaries between the two TIMESTAMPs for the corresponding unit, as computed by the datediff function:
For equality and ordering comparisons only, the total number of microseconds in an INTERVAL is computed by converting the days basis unit to 24 * 60 * 60 * 1e6 microseconds and the months basis unit to 30 days, or 30 * 24 * 60 * 60 * 1e6 microseconds.
As a result, INTERVALs can compare equal even when they are functionally different, and the ordering of INTERVALs is not always preserved when they are added to dates or timestamps.
For example:
INTERVAL 30 DAYS = INTERVAL 1 MONTH
but DATE '2020-01-01' + INTERVAL 30 DAYS != DATE '2020-01-01' + INTERVAL 1 MONTH.
and
INTERVAL '30 days 12 hours' > INTERVAL 1 MONTH
but DATE '2020-01-01' + INTERVAL '30 days 12 hours' < DATE '2020-01-01' + INTERVAL 1 MONTH.