INTERVAL type

  • Article

Applies to: check marked yes Databricks SQL check marked yes 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