Home  /  Reference  /  SQL data types

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 -2562047788:00:54.775808
Max value 178956970 years 7 months 2147483647 days 2562047788:00:54.775807

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:

  • 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:

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
interval / double precision 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
-------------
 -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
Back to top ↑