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


You can't use the Interval data types for storing values in tables.


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

Supported interval types:

  • HOUR
  • DAY
  • WEEK
  • YEAR

For each interval type, there is the separated data type. For example, the DAY interval is expressed as the IntervalDay data type:

│ 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 │

Intervals of different types can't be combined. You can't use intervals like 4 DAY 1 HOUR, express intervals in the units that smaller or equal the the smallest unit of the interval. For example, 1 day and an hour interval can be expressed as 25 HOUR or 90000 SECOND.

You can't perform arithmetical operations with the Interval-type values, but you can add intervals of different types consequently to some value. For example:

SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY + INTERVAL 3 HOUR
┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2019-10-23 11:16:28 │                                    2019-10-27 14:16:28 │

The following query causes the exception:

select now() AS current_date_time, current_date_time + (INTERVAL 4 DAY + INTERVAL 3 HOUR)
Received exception from server (version 19.14.1):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Wrong argument types for function plus: if one argument is Interval, then another must be Date or DateTime.. 

See Also