INTERVAL
type
Applies to: Databricks SQL Databricks Runtime
Represents intervals of time either on a scale of seconds or months.
Syntax
INTERVAL { yearMonthIntervalQualifier | dayTimeIntervalQualifier }
yearMonthIntervalQualifier
{ YEAR [TO MONTH] |
MONTH }
dayTimeIntervalQualifier
{ DAY [TO { HOUR | MINUTE | SECOND } ] |
HOUR [TO { MINUTE | SECOND } ] |
MINUTE [TO SECOND] |
SECOND }
Notes
Intervals covering years or months are called year-month intervals.
Intervals covering days, hours, minutes, or seconds are called day-time intervals.
You cannot combine or compare year-month and day-time intervals.
Day-time intervals are strictly based on 86400s/day and 60s/min.
Seconds are always considered to include microseconds.
Limits
A year-month interval has a maximal range of +/- 178,956,970
years and 11
months.
A day-time interval has a maximal range of +/- 106,751,991
days, 23
hours, 59
minutes, and 59.999999
seconds.
Literals
year-month interval
INTERVAL [+|-] yearMonthIntervalString yearMonthIntervalQualifier
day-time interval
INTERVAL [+|-] dayTimeIntervalString dayTimeIntervalQualifier
yearMonthIntervalString
{ '[+|-] y[...]' |
'[+|-] y[...]-[m]m' }
dayTimeIntervalString
{ '[+|-] d[...]' |
'[+|-] d[...] [h]h' |
'[+|-] d[...] [h]h:[m]m' |
'[+|-] d[...] [h]h:[m]m:[s]s' |
'[+|-] d[...] [h]h:[m]m:[s]s.ms[ms][ms][us][us][us]' |
'[+|-] h[...]' |
'[+|-] h[...]:[m]m' |
'[+|-] h[...]:[m]m:[s]s' |
'[+|-] h[...]:[m]m:[s]s.ms[ms][ms][us][us][us]' |
'[+|-] m[...]' |
'[+|-] m[...]:[s]s' |
'[+|-] m[...]:[s]s.ms[ms][ms][us][us][us]' |
'[+|-] s[...]' |
'[+|-] s[...].ms[ms][ms][us][us][us]' }
y
: The elapsed number of years.m
: The elapsed number of months.d
: The elapsed number of days.h
: The elapsed number of hours.m
: The elapsed number of minutes.s
: The elapsed number of seconds.ms
: The elapsed number of milliseconds.us
: The elapsed number of microseconds.
Unless a unit constitutes the leading unit of the intervalQualifier
it must fall within the defined range:
Months: between 0 and 11
Hours: between 0 and 23
Minutes: between 0 and 59
Seconds: between 0.000000 and 59.999999
You can prefix a sign either inside or outside intervalString
.
If there is one -
sign, the interval is negative. If there are two or no -
signs, the interval is positive.
If the components in the intervalString
do not match up with the components in the intervalQualifier
an error is raised.
If the intervalString
value does not fit into the range specified by the intervalQualifier
an error is raised.
Examples
> SELECT INTERVAL '100-00' YEAR TO MONTH;
100-0
> SELECT INTERVAL '-3600' MONTH;
-300-0
> SELECT INTERVAL -'200:13:50.3' HOUR TO SECOND;
-200:13:50.3
> SELECT typeof(INTERVAL -'200:13:50.3' HOUR TO SECOND);
interval hour to second
> SELECT CAST('11 23:4:0' AS INTERVAL DAY TO SECOND);
11 23:04:00