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.

Syntax

INTERVAL

time_expr

time_unit

Details

time_expr Syntax

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

• SQL Standard, i.e. 'Y-M D H:M:S.NS'
• PostgreSQL, i.e. repeated int.frac time_unit, e.g.:
  • '1 year 2 months 3.4 days 5 hours 6 minutes 7 seconds 8 milliseconds'
  • '1y 2mon 3.4d 5h 6m 7s 8ms'

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

• You can freely mix SQL Standard- and PostgreSQL-style time_exprs.

• You can write time_exprs in any order, e.g 'H:M:S.NS Y-M'.

• Each time_unit can only be written once.

• SQL Standard time_expr uses the following groups of time_units:

  • Y-M
  • D
  • H:M:S.NS

  Using a SQL Standard time_expr to write to any of these time_units writes to all other time_units in the same group, even if that time_unit is not explicitly referenced.

  For example, the time_expr '1:2' (1 hour, 2 minutes) also writes a value of 0 seconds. You cannot then include another time_expr which writes to the seconds time_unit.

• A two-field time_expr like '1:2' is by default interpreted as (hour, minute) while 1:2 MINUTE TO SECOND interprets as (minute, seconds).

• Only PostgreSQL time_exprs support non-second fractional time_units, e.g. 1.2 days. Materialize only supports 9 places of decimal precision.

Valid casts

From interval

You can cast interval to:

• text (by assignment)
• time (by assignment)

To interval

You can cast from the following types to interval:

• text (explicitly)
• time (explicitly)

Valid operations

interval data supports the following operations with other types.

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