Materialize Documentation
s
Join the Community github/materialize

interval type

interval data expresses a duration of time.

interval data keeps months, days, and microseconds completely separate and will not try to convert between any of those fields when comparing intervals. This may lead to some unexpected behavior. For example 1 month is considered greater than 100 days. See ‘justify_days’, ‘justify_hours’, and ‘justify_interval’ to explicitly convert between these fields.

Detail Info
Quick Syntax INTERVAL '1' MINUTE
INTERVAL '1-2 3 4:5:6.7'
INTERVAL '1 year 2.3 days 4.5 seconds'
Size 20 bytes
Catalog name pg_catalog.interval
OID 1186
Min value -178956970 years -8 months -2147483648 days -2147483648:59:59.999999
Max value 178956970 years 7 months 2147483647 days 2147483647:59:59.999999

Syntax

INTERVAL

INTERVAL ' time_expr ' head_time_unit TO tail_time_unit

time_expr

+ - ym_str time_str int . frac time_unit

time_unit

MILLENNIUM CENTURY DECADE YEAR MONTH DAY HOUR MINUTE SECOND MILLISECONDS MICROSECONDS
Field Use
ym_str A string representing years and months in Y-M D format.
time_str A string representing hours, minutes, seconds, and nanoseconds in H:M:S.NS format.
head_time_unit Return an interval without time_units larger than head_time_unit. Note that this differs from PostgreSQL’s implementation, which ignores this clause.
tail_time_unit 1. Return an interval without time_unit smaller than tail_time_unit.

2. If the final time_expr is only a number, treat the time_expr as belonging to tail_time_unit. This is the case of the most common interval format like INTERVAL '1' MINUTE.

Details

time_expr Syntax

Materialize strives for full PostgreSQL compatibility with time_exprs, which offers support for two types of time_expr syntax:

Like PostgreSQL, Materialize’s implementation includes the following stipulations:

Valid casts

From interval

You can cast interval to:

To interval

You can cast from the following types to interval:

Valid operations

interval data supports the following operations with other types.

Operation Computes Notes
date + interval timestamp
date - interval timestamp
date - date interval
timestamp + interval timestamp
timestamp - interval timestamp
timestamp - timestamp interval
time + interval time
time - interval time
time - time interval
interval * double precision interval

New in v0.6.1.

interval / double precision interval

New in v0.6.1.

Examples

SELECT INTERVAL '1' MINUTE AS interval_m;
 interval_m
------------
 00:01:00

SQL Standard syntax

SELECT INTERVAL '1-2 3 4:5:6.7' AS interval_p;
            interval_f
-----------------------------------
 1 year 2 months 3 days 04:05:06.7

PostgreSQL syntax

SELECT INTERVAL '1 year 2.3 days 4.5 seconds' AS interval_p;
        interval_p
--------------------------
 1 year 2 days 07:12:04.5

Negative intervals

interval_n demonstrates using negative and positive components in an interval.

SELECT INTERVAL '-1 day 2:3:4.5' AS interval_n;
 interval_n
-------------
 -1 days +02:03:04.5

Truncating interval

interval_r demonstrates how head_time_unit and tail_time_unit truncate the interval.

SELECT INTERVAL '1-2 3 4:5:6.7' DAY TO MINUTE AS interval_r;
   interval_r
-----------------
 3 days 04:05:00

Complex example

interval_w demonstrates both mixing SQL Standard and PostgreSQL time_expr, as well as using tail_time_unit to control the time_unit of the last value of the interval string.

SELECT INTERVAL '1 day 2-3 4' MINUTE AS interval_w;
           interval_w
---------------------------------
 2 years 3 months 1 day 00:04:00

Interaction with timestamps

SELECT TIMESTAMP '2020-01-01 8:00:00' + INTERVAL '1' DAY AS ts_interaction;
   ts_interaction
---------------------
 2020-01-02 08:00:00