Interval

The family of data types representing time and date intervals. The resulting types of the INTERVAL operator. Structure:

Time interval as an unsigned integer value.
Type of an interval.

Supported interval types:

NANOSECOND
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

For each interval type, there is a separate data type. For example, the DAY interval corresponds to the IntervalDay data type:

SELECT toTypeName(INTERVAL 4 DAY)

┌─toTypeName(toIntervalDay(4))─┐
│ IntervalDay                  │
└──────────────────────────────┘

Usage Remarks

You can use Interval-type values in arithmetical operations with Date and DateTime-type values. For example, you can add 4 days to the current time:

SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY

┌───current_date_time─┬─plus(now(), toIntervalDay(4))─┐
│ 2019-10-23 10:58:45 │           2019-10-27 10:58:45 │
└─────────────────────┴───────────────────────────────┘

Also it is possible to use multiple intervals simultaneously:

SELECT now() AS current_date_time, current_date_time + (INTERVAL 4 DAY + INTERVAL 3 HOUR)

┌───current_date_time─┬─plus(current_date_time, plus(toIntervalDay(4), toIntervalHour(3)))─┐
│ 2024-08-08 18:31:39 │                                                2024-08-12 21:31:39 │
└─────────────────────┴────────────────────────────────────────────────────────────────────┘

And to compare values with different intervals:

SELECT toIntervalMicrosecond(179999999) < toIntervalMinute(3);

┌─less(toIntervalMicrosecond(179999999), toIntervalMinute(3))─┐
│                                                           1 │
└─────────────────────────────────────────────────────────────┘

SELECT toIntervalMicrosecond(3600000000) = toIntervalHour(1);

┌─equals(toIntervalMicrosecond(3600000000), toIntervalHour(1))─┐
│                                                            1 │
└──────────────────────────────────────────────────────────────┘

Aggregation

sum, avg, min, max and any over an Interval column return an interval of the same type. sum adds up the number of units, and avg averages them and rounds the result to a whole number of them:

SELECT
    sum(duration) AS total_duration,
    avg(duration) AS avg_duration,
    toTypeName(total_duration)
FROM VALUES('duration IntervalMillisecond',
    toIntervalMillisecond(100), toIntervalMillisecond(200), toIntervalMillisecond(300))

┌─total_duration─┬─avg_duration─┬─toTypeName(total_duration)─┐
│            600 │          200 │ IntervalMillisecond        │
└────────────────┴──────────────┴────────────────────────────┘

Values of different units are brought to their common unit before they are aggregated, so summing a minute and a second gives 61 seconds. Month, Quarter and Year have no common unit with the smaller ones - a month is not a fixed number of days - and cannot be mixed with them at all.

Mixed-type Intervals

Intervals of mixed type, e.g. multiple hours and multiple minutes, can be created using INTERVAL 'value' <from_kind> TO <to_kind> syntax. The result is a tuple of two or more intervals. Supported combinations:

Syntax	String format	Example
`YEAR TO MONTH`	`Y-M`	`INTERVAL '2-6' YEAR TO MONTH`
`DAY TO HOUR`	`D H`	`INTERVAL '5 12' DAY TO HOUR`
`DAY TO MINUTE`	`D H:M`	`INTERVAL '5 12:30' DAY TO MINUTE`
`DAY TO SECOND`	`D H:M:S`	`INTERVAL '5 12:30:45' DAY TO SECOND`
`HOUR TO MINUTE`	`H:M`	`INTERVAL '1:30' HOUR TO MINUTE`
`HOUR TO SECOND`	`H:M:S`	`INTERVAL '1:30:45' HOUR TO SECOND`
`MINUTE TO SECOND`	`M:S`	`INTERVAL '5:30' MINUTE TO SECOND`

Non-leading fields are validated per the SQL standard: MONTH 0-11, HOUR 0-23, MINUTE 0-59, SECOND 0-59.

SELECT INTERVAL '1:30' HOUR TO MINUTE;

┌─(toIntervalHour(1), toIntervalMinute(30))─┐
│ (1,30)                                     │
└────────────────────────────────────────────┘

An optional leading + or - sign applies to all components:

SELECT INTERVAL '+1:30' HOUR TO MINUTE;
-- this is equivalent to:
-- SELECT INTERVAL '1:30' HOUR TO MINUTE;

┌─(toIntervalHour(1), toIntervalMinute(30))─┐
│ (1,30)                                     │
└────────────────────────────────────────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Usage Remarks

Aggregation

Mixed-type Intervals

See Also

​Usage Remarks

​Aggregation

​Mixed-type Intervals

​See Also

Usage Remarks

Aggregation

Mixed-type Intervals

See Also