Arithmetic with intervals

This topic describes the Firebolt implementation of arithmetic with intervals.

You are looking at the documentation for Firebolt’s redesigned date and timestamp types. These types were introduced in DB version 3.19 under the names PGDATE, TIMESTAMPNTZ and TIMESTAMPTZ, and synonyms DATE, TIMESTAMP and TIMESTAMPTZ made available in DB version 3.22.

If you worked with Firebolt before DB version 3.22, you might still be using the legacy date and timestamp types. Determine which types you are using by executing the query SELECT EXTRACT(CENTURY FROM DATE '2023-03-16');. If this query returns a result, you are using the redesigned date and timestamp types and can continue with this documentation. If this query returns an error, you are using the legacy date and timestamp types and can find legacy documentation here, or instructions to reingest your data to use the new types here.

• Overview
• Literal string interpretation
  • First format
  • Second format
• Arithmetic between interval and TIMESTAMPTZ
  • Multiplying an interval by a scalar
• Examples

Overview

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). Although the arithmetic operators check that the resulting timestamp is in the supported range, they don’t check for integer overflow.

Literal string interpretation

Interval literals can be specified in two formats.

First format

interval 'quantity unit [quantity unit...] [direction]'

where direction can be ago or empty (ago negates all the quantities), quantity is a possibly signed integer, and unit is one of the following, matched case-insensitively:

Each unit can appear only once in an interval literal. Not all months have the same number of days. Additionally, not all days consist of 24 hours because of daylight savings time transitions for TIMESTAMPTZ. 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').

The value of the interval is determined by adding the quantities of the specified units with the appropriate signs, and an interval literal is valid if:

1. The quantities used for millennium, century, decade, year, and month can be represented as a number of months in the range [-pow(2, 31), pow(2, 31) - 1],
2. The quantities used for week and day can be represented as a number of days in the range [-pow(2, 31), pow(2, 31) - 1],
3. The quantities used for hour, minute, second, millisecond, and microsecond can be represented as a number of microseconds in the range [-pow(2, 63), pow(2, 63) - 1].

Second format

interval 'N' unit

where N is a possibly signed integer, and unit is one of the following, matched case-insensitively:

Arithmetic between interval and TIMESTAMPTZ

Interval arithmetic with TIMESTAMPTZ values works as follows:

1. Convert the TIMESTAMPTZ value from Unix time to local time according to the rules of the time zone specified by the session’s time_zone setting.
2. Add the months and days components of the interval to the local time.
3. Convert the local time back to Unix time according to the rules of the time zone specified by the session’s time_zone setting.
4. Add the microseconds component 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. Still, the dependence on the session’s time_zone setting should be kept in mind when doing arithmetic between interval and TIMESTAMPTZ.

Multiplying an interval by a scalar

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'.

Examples

SELECT DATE '1996-09-03' - interval '1 millennium 5 years 42 day 42 ms';  --> 0991-07-22 23:59:59.958
SELECT TIMESTAMP '1996-09-03 11:19:42' + interval '10 years 5 months 42 days 7 seconds';  --> 2007-03-17 11:19:49

SELECT TIMESTAMP '2023-10-20 11:49:52' + interval '1 year 6 months 4 weeks 7 hours' * 7.5;  --> 2035-08-20 16:19:52
SELECT DATE '2023-10-20' - 42 * interval '1 months 1 day 1 hour';  --> 2020-03-07 06:00:00

-- The following example shows a daylight savings time change in the time zone 'Europe/Berlin'
SET time_zone = 'Europe/Berlin';
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day';  --> 2022-10-31 00:00:00+01
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24' hour;  --> 2022-10-30 23:00:00+01

SET time_zone = 'US/Pacific';
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '1 day';  --> 2022-10-30 15:00:00-07
SELECT TIMESTAMPTZ '2022-10-30 Europe/Berlin' + interval '24' hour;  --> 2022-10-30 15:00:00-07