INTERVAL

INTERVAL Data Type

Lakehouse provides the INTERVAL data type to represent the time interval between two dates or times. This article introduces the usage and syntax of the INTERVAL type.

Description

The INTERVAL data type supports two types of intervals:

INTERVAL_YEAR_MONTH: Represents year-month intervals, using the YEAR and MONTH fields to store the time interval.
INTERVAL_DAY_TIME: Represents day-time intervals, using days, hours, minutes, and seconds, including fractional seconds, to store the interval.

INTERVAL_YEAR_MONTH

Syntax Format

Syntax	Description	Example
INTERVAL '[+ \| -]-' YEAR TO MONTH	Specifies both YEAR and MONTH intervals	INTERVAL '2-3' YEAR TO MONTH represents 2 years and 3 months
INTERVAL '[+ \| -]' YEAR	Specifies only the YEAR interval	INTERVAL '2' YEAR represents 2 years
INTERVAL '[+\| -]' MONTH	Specifies only the MONTH interval	INTERVAL '3' MONTH represents 3 months

Parameter Description

year: The range is [0, 9999].
month: The range is [0, 11].

Notes

When specifying only the MONTH interval, the value of month can exceed 11, and the excess will be converted to YEAR for calculation.

INTERVAL_DAY_TIME

Syntax Format

Syntax	Description	Example
INTERVAL '[+ \| -]' DAY	Specifies only the DAY interval	INTERVAL '1' DAY represents 1 day
INTERVAL '[+ \| -]' HOUR	Specifies only the HOUR interval	INTERVAL '23' HOUR represents 23 hours
INTERVAL '[+ \| -]' MINUTE	Specifies only the MINUTE interval	INTERVAL '59' MINUTE represents 59 minutes
INTERVAL '[+ \| -]' SECOND	Specifies only the SECOND interval	INTERVAL '59.999' SECOND represents 59.999 seconds
INTERVAL '[+ \| -] ' DAY TO HOUR	Specifies both DAY and HOUR intervals	INTERVAL '1 23' DAY TO HOUR represents 1 day and 23 hours
INTERVAL '[+ \| -] ' DAY TO MINUTE	Specifies DAY, HOUR, and MINUTE intervals	INTERVAL '1 23:59' DAY TO MINUTE represents 1 day, 23 hours, and 59 minutes
INTERVAL '[+ \| -] ' DAY TO SECOND	Specifies intervals for DAY, HOUR, MINUTE, and SECOND simultaneously	INTERVAL '1 23:59:59.999' DAY TO SECOND represents 1 day, 23 hours, 59 minutes, and 59.999 seconds

The INTERVAL expr unit allows time interval expressions. For example:


select interval 1+2 year as res;
+-----+
| res |
+-----+
| 3-0 |
+-----+

Parameter Description

day: Value range is [0, 2147483647].
hour: Value range is [0, 23].
minute: Value range is [0, 59].
second: Value range is [0, 59.999999999].

Notes

When only specifying HOUR/MINUTE/SECOND intervals, the corresponding parameter values can exceed the upper limit of the range. The excess part will be converted to a larger unit.

interval Type Operations

interval type can perform some simple arithmetic operations with numeric types and date-time types


> SELECT INTERVAL 4 DAY * 2, INTERVAL 4 DAY  / 2;
8 00:00:00.000000000    2 00:00:00.000000000

> SELECT timestamp '2019-10-15' - timestamp '2019-10-14', date '2020-10-15' - date '2019-10-14';
1 00:00:00.000000000    367 00:00:00.000000000

> SELECT timestamp '2020-10-10' + INTERVAL 1 DAY, date '2020-10-10' + INTERVAL 1 MONTH;
2020-10-11 00:00:00     2020-11-10

> SELECT INTERVAL 1 DAY < INTERVAL 2 DAY;
true

interval type cast

When the interval type is cast to a string, it will include type information;


> SELECT cast('INTERVAL 1 DAY' AS INTERVAL DAY TO SECOND), cast('INTERVAL 1 DAY' AS INTERVAL DAY);
NULL

interval type writing is more flexible


SELECT    interval 7 week;
+--------------------------------------+
| INTERVAL '49 00:00:00' DAY TO SECOND |
+--------------------------------------+
| 49 00:00:00.000000000                |
+--------------------------------------+

The interval can be written in the entire string


SELECT    'interval 7 week';
+-------------------+
| 'interval 7 week' |
+-------------------+
| interval 7 week   |
+-------------------+

The interval number can be written into the string


SELECT    interval '7' week;
+--------------------------------------+
| INTERVAL '49 00:00:00' DAY TO SECOND |
+--------------------------------------+
| 49 00:00:00.000000000                |
+--------------------------------------+

Numbers and time units can be written into the entire string


SELECT    interval '7 week';
+--------------------------------------+
| INTERVAL '49 00:00:00' DAY TO SECOND |
+--------------------------------------+
| 49 00:00:00.000000000                |
+--------------------------------------+