Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Interval

View as MarkdownEdit this pageReport an issueSet up docs MCP

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 │
└──────────────────────────────────────────────────────────────┘

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)                                     │
└────────────────────────────────────────────┘

See Also

Navigation