ESQL INTERVAL data type

The INTERVAL data type holds an interval of time.

This data type has a number of subtypes:

  • YEAR
  • YEAR TO MONTH
  • MONTH
  • DAY
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE
  • MINUTE TO SECOND
  • SECOND

All these subtypes describe intervals of time and all can take part in the full range of operations of the INTERVAL type; for example, addition and subtraction operations with values of type DATE, TIME, or TIMESTAMP.

Use the CAST function to convert from one subtype to another, except for intervals described in years and months, or months, which cannot be converted to those described in days, hours, minutes, and seconds.

The split between months and days arises because the number of days in each month varies. An interval of one month and a day is not meaningful, and cannot be sensibly converted into an equivalent interval in numbers of days only.

An interval literal is defined by the syntax: 

INTERVAL <interval string> <interval qualifier>

The format of interval string and interval qualifier are defined by the following table.

Interval qualifier Interval string format Examples
YEAR [sign]'[sign]year' INTERVAL '10' YEAR (next 10 years)

INTERVAL '-10' YEAR (previous 10 years)

INTERVAL -'10' YEAR (previous 10 years)

INTERVAL -'-10' YEAR (next 10 years; two negatives make a positive)
YEAR TO MONTH [sign]'[sign]year-month' INTERVAL '2-06' YEAR TO MONTH
MONTH [sign]'[sign]month' INTERVAL '18' MONTH
DAY [sign]'[sign]day' INTERVAL '30' DAY
DAY TO HOUR [sign]'[sign]day hour' INTERVAL '1 02' DAY TO HOUR
DAY TO MINUTE [sign]'[sign]day hour:minute' INTERVAL '1 02:30' DAY TO MINUTE
DAY TO SECOND [sign]'[sign]day hour:minute:second' INTERVAL '1 02:30:15' DAY TO SECOND
HOUR [sign]'[sign]hour' INTERVAL '24' HOUR
HOUR TO MINUTE [sign]'[sign]hour:minute' INTERVAL '1:30' HOUR TO MINUTE
HOUR TO SECOND [sign]'[sign]hour:minute:second' INTERVAL '1:29:59' HOUR TO SECOND
MINUTE [sign]'[sign]minute' INTERVAL '90' MINUTE
MINUTE TO SECOND [sign]'[sign]minute:second' INTERVAL '89:59' MINUTE TO SECOND
SECOND [sign]'[sign]second' INTERVAL '15' SECOND

Where an interval contains both a year and a month value, a hyphen is used between the two values. In this instance, the month value must be within the range [0,11]. If an interval contains a month value and no year value, the month value is unconstrained.

A space is used to separate days from the rest of the interval.

If an interval contains more than one of HOUR, MINUTE, and SECOND, a colon is needed to separate the values and all except the leftmost are constrained as follows:

HOUR
0-23
MINUTE
0-59
SECOND
0-59.999...

The largest value of the leftmost value in an interval is +/- 2147483647.

Some examples of valid interval values are:

  • 72 hours
  • 3 days: 23 hours
  • 3600 seconds
  • 90 minutes: 5 seconds

Some examples of invalid interval values are:

  • 3 days: 36 hours

    A day field is specified, so the hours field is constrained to [0,23].

  • 1 hour: 90 minutes

    An hour field is specified, so minutes are constrained to [0,59].

The following code is an example of how to define INTERVAL literals in your code:

DECLARE i1,i2,i3,i4,i5 INTERVAL;
SET i1 = INTERVAL '10' YEAR;     
SET i2 = INTERVAL '-10' YEAR;    
SET i3 = INTERVAL -'10' YEAR;    
SET i4 = INTERVAL -'-10' YEAR;  
SET i5 = INTERVAL '-2-06' YEAR TO MONTH;