Materialize Logo

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 20 bytes
Min value -178956970 years -7 months -2236962132 days -07:59:59.999999
Max value 178956970 years 7 months 2236962132 days 07: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

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

You can cast the following types to interval:

Valid operations

interval data supports the following operations with other types.

Operation Computes
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

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