INTERVAL type

Represents intervals of time either on a scale of seconds or months.

Syntax

INTERVAL intervalQualifier

intervalQualifier:
  YEAR  [ ( n ) ] [TO MONTH] |
  MONTH [ ( n ) ] |
  DAY [ ( n ) ] [TO { HOUR | MINUTE | SECOND [ ( 6 ) ] } ] |
  HOUR  [ ( n ) ] [TO { MINUTE | SECOND [ ( 6 ) ] } ] |
  MINUTE  [ ( n ) ] [TO SECOND [ ( 6 ) ] ] |
  SECOND [ ( 6 ) ]

n: The optional maximum number of units of the leading unit. n must be greater than 0 and smaller or equal to:

  • 8 for years
  • 9 for months
  • 8 for days
  • 9 for hours
  • 11 for minutes

If n is not specified it defaults to the unit specific maximum.

Notes

Intervals covering years or months are called year-month intervals. Intervals covering days, hours, minutes, or seconds are called day-second intervals. You cannot combine or compare year-month and day-second intervals. Day-second 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 +/- 999,999,999 months. A day-second interval has a maximal range of +/- 999,999,999,999.999999 seconds.

Literals

INTERVAL [+|-] intervalString intervalQualifier

intervalString:
{ '[+|-] y[y …]' |
  '[+|-] y[y …]-[m]m' |
  '[+|-] d[d …]' |
  '[+|-] d[d …][T][h]h' |
  '[+|-] d[d …][T][h]h:[m]m' |
  '[+|-] d[d …][T][h]h:[m]m:[s]s' |
  '[+|-] d[d …][T][h]h:[m]m:[s]s.ms[ms][ms][us][us][us]' |
  '[+|-] h[h …]' |
  '[+|-] h[h …]:[m]m' |
  '[+|-] h[h …]:[m]m:[s]s' |
  '[+|-] h[h …]:[m]m:[s]s.ms[ms][ms][us][us][us]' |
  '[+|-] m[m …]' |
  '[+|-] m[m …]:[s]s' |
  '[+|-] m[m …]:[s]s.ms[ms][ms][us][us][us]' |
  '[+|-] s[s …]' |
  '[+|-] 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;
  INTERVAL 100-100 YEAR TO MONTH

> SELECT INTERVAL '-3600' MONTH(4);
  INTERVAL -3600 MONTH(4)

> SELECT INTERVAL -'200:13:50.3' HOUR(6) TO SECOND(6)
  INTERVAL -200:13:50.3 HOUR(6) TO SECOND(6)

> SELECT CAST('11T23:4:0' AS INTERVAL DAY(3) TO SECOND)
  INTERVAL 11T23:04:00 DAY(3) TO SECOND(6)