Describes the Firebolt implementation of arithmetic with intervals
This topic describes the Firebolt implementation of arithmetic with intervals.
An interval
represents a duration.
In Firebolt, values of type interval
can be used to add or subtract a duration to/from a date or timestamp.
Interval
cannot be used as the data type of a column.
The +
and *
operators shown below come in commutative pairs (e.g., both DATE + interval
and interval + DATE
are accepted).
Operator | Description |
---|---|
DATE + interval -> TIMESTAMP | Add an interval to a DATE |
DATE - interval -> TIMESTAMP | Subtract an interval from a DATE |
TIMESTAMP + interval -> TIMESTAMP | Add an interval to a TIMESTAMP |
TIMESTAMP - interval -> TIMESTAMP | Subtract an interval from a TIMESTAMP |
TIMESTAMPTZ + interval -> TIMESTAMPTZ | Add an interval to a TIMESTAMPTZ |
TIMESTAMPTZ - interval -> TIMESTAMPTZ | Subtract an interval from a TIMESTAMPTZ |
interval * DOUBLE PRECISION -> interval | Multiply an interval by a scalar |
Interval
literals can be specified in two formats.
Cast from text literal
The following examples demonstrate different ways to cast a text literal to an interval in SQL. Each method converts a string representing a time interval into the INTERVAL
data type using various casting syntaxes.
Using interval keyword:
Using double colon:
Using CAST
function:
In these examples, direction
can be either ago
or left empty. Using ago
negates all specified quantities. The quantity
represents a signed or unsigned integer, and unit
refers to one of the following time units, matched case-insensitively:
Unit |
---|
microsecond[s] / us |
millisecond[s] / ms |
second[s] / s |
minute[s] / m |
hour[s] / h |
day[s] / d |
week[s] / w |
month[s] / mon[s] |
year[s] / y |
decade[s] / dec[s] |
century / centuries / c |
millennium[s] / mil[s] |
Each unit
can appear only once in an interval literal.
The value of the interval is determined by adding the quantities of the specified units with the appropriate signs.
Unit outside of text literal
The following example demonstrates how to cast a numeric value to an interval by placing the unit outside of the text literal.
In this format, N
represents a signed or unsigned integer, and unit
specifies the time unit, matched case-insensitively, from the following options:
Unit |
---|
second |
minute |
hour |
day |
week |
month |
year |
Interval arithmetic with TIMESTAMPTZ
values works as follows:
TIMESTAMPTZ
value from Unix time to local time according to the rules of the time zone specified by the session’s time_zone
setting.millennium
, century
, decade
, year
, month
, week
and day
components of the interval to the local time.time_zone
setting.hour
, minute
, second
, millisecond
, and microsecond
components of the interval to the Unix time.The back and forth between Unix time and local time is necessary to handle the fact that not all days consist of 24 hours due to daylight savings time transitions.
For instance, SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day'
returns 2022-10-31 00:00:00+01
but SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24 hours'
returns 2022-10-30 23:00:00+01
(assuming the value of the session’s time_zone
setting is 'Europe/Berlin'
).
Still, the dependence on the session’s time_zone
setting should be kept in mind when doing arithmetic between interval and TIMESTAMPTZ
.
You can use the expression date_time + INTERVAL * d
where date_time
is a constant or column reference of type DATE
, TIMESTAMP
, or TIMESTAMPTZ
, and d
is a constant or column reference of type DOUBLE PRECISION
.
The effect is that the INTERVAL is scaled by d
, and the resulting INTERVAL is added to date_time
.
E.g., INTERVAL '1 day' * 3
-> INTERVAL '3 days'
.