Skip to main content

Documentation Index

Fetch the complete documentation index at: https://private-7c7dfe99-page-updates.mintlify.app/llms.txt

Use this file to discover all available pages before exploring further.

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(3600000000) = toIntervalHour(1);

┌─less(toIntervalMicrosecond(179999999), toIntervalMinute(3))─┐
│                                                           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:
SyntaxString formatExample
YEAR TO MONTHY-MINTERVAL '2-6' YEAR TO MONTH
DAY TO HOURD HINTERVAL '5 12' DAY TO HOUR
DAY TO MINUTED H:MINTERVAL '5 12:30' DAY TO MINUTE
DAY TO SECONDD H:M:SINTERVAL '5 12:30:45' DAY TO SECOND
HOUR TO MINUTEH:MINTERVAL '1:30' HOUR TO MINUTE
HOUR TO SECONDH:M:SINTERVAL '1:30:45' HOUR TO SECOND
MINUTE TO SECONDM:SINTERVAL '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