SQL
Interval Type
`INTERVAL`s represent periods of time that can be added to or subtracted from `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`, or `TIME` values.  
<div class="narrow_table"></div>  
| 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.  
```sql
SELECT
INTERVAL 1 YEAR, -- single unit using YEAR keyword; stored as 12 months
INTERVAL (random() * 10) YEAR, -- parentheses necessary for variable amounts;
-- stored as integer number of months
INTERVAL '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.
> ```sql
> SELECT INTERVAL '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:  
```sql
SELECT DATE '2000-01-01' + INTERVAL (i) MONTH
FROM range(12) t(i);
```  
When `INTERVAL`s 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`.  
```sql
SELECT
period = list_reduce(
[INTERVAL (datepart(part, period) || part) FOR part IN
['year', 'month', 'day', 'hour', 'minute', 'microsecond']
],
(i1, i2) -> i1 + i2
) -- always true
FROM (
VALUES (
INTERVAL (random() * 123_456_789_123) MICROSECONDS
+ INTERVAL (random() * 12_345) DAYS
+ INTERVAL (random() * 12_345) MONTHS
)
) _(period);
```  
> **Warning.  ** 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.  
> All units use 0-based indexing, except for quarters, which use 1-based indexing.  
For example:  
```sql
SELECT
datepart('decade', INTERVAL 12 YEARS), -- returns 1
datepart('year', INTERVAL 12 YEARS), -- returns 12
datepart('second', INTERVAL 1_234 MILLISECONDS), -- returns 1
datepart('microsecond', INTERVAL 1_234 MILLISECONDS), -- returns 1_234_000
```