interval Data Type

interval data expresses a duration of time.

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 32 bytes
Min value -9223372036854775807 months, -9223372036854775807 seconds, -999999999 nanoseconds
Max value 9223372036854775807 months, 9223372036854775807 seconds, 999999999 nanoseconds

Syntax 

INTERVAL 

INTERVAL ' time_expr ' head_time_unit TO tail_time_unit

time_expr 

+ - Y-M H:M:S.NS int . frac time_unit

time_unit 

YEAR MONTH DAY HOUR MINUTE SECOND
Field Definition
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 

interval does not support any casts.

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
-------------
 -21:56:55.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.000000000