Basic elements of SQL : Literals : Interval literals

Interval literals

Interval literal strings can be used in SQL queries to manipulate Date/Time/Timestamp values. Interval literal strings can be used in date-time expression to:

• Add an interval to a datetime (or add a datetime to an interval)

• Subtract an interval from a datetime

The interval literal string can be in one of these formats:

• {INTERVAL [+|-] interval-string interval-qualifier}

• INTERVAL [+|-] interval-string interval-qualifier

Note: You can only use one of these formats in any expression; they cannot be combined.

where:

Interval-string is specified in quotation marks in one of two formats:

• Year-month interval: interval in terms of years and an integral number of months

• Day-time interval: interval in terms of days, hours, minutes, seconds.

Each field in the interval string must be separated by a separator character as follows:

Between year and month:	hyphen
Between day and hour:	space
Between hour and minute:	colon
Between minute and second:	colon
Between second and fractional second:	period

Date-time interval operations are supported only on dates more recent than January 1, 1970. Year-month interval operations are supported on any date.

Interval-qualifier: The interval qualifier specifies the class of the interval (type of interval) and precision of the fields.

The following table describes the supported interval literals.

Note: The current version of the software does not support the Interval data type.

Table 1: Interval literal examples
Format	Description
{INTERVAL '5' YEAR}	interval of 5 years
{INTERVAL '5' YEAR(2)}	interval of 5 years. The interval leading precision is 2.
{INTERVAL '20' MONTH}	interval of 20 months
{INTERVAL '5-20' YEAR To MONTH} -	interval of 2 years and 20 months
{INTERVAL '2' DAY}	interval of 2 days
{INTERVAL '2 10' DAY To HOUR}	interval of 2 days and 10 hours
{INTERVAL '2 10:20' DAY To MINUTE} -	interval of 2 days, 10 hours and 20 minutes
{INTERVAL '2 10:20:5' DAY To SECOND}	interval of 2 days, 10 hours, 20 minutes, 5 seconds
{INTERVAL '2 10:20:5.123' DAY To SECOND}	interval of 2 days, 10 hours, 20 minutes, 5.123 seconds

Example Date-Time expression queries with interval literals

SELECT * FROM archive WHERE time BETWEEN now() - {INTERVAL '1' DAY} and now();

   SELECT * FROM emp WHERE hiredate BETWEEN (now()) - (interval '1' day) and now();
  

Syntax In BNF ntation

<interval-literal> ::= INTERVAL [+|-] <interval-string>

<interval-qualifier>

<interval-string> ::= <quote> { <year-month-literal>

| <day-time-literal> } <quote>

<year-month-literal> ::= <years-value>

| [<years-value> -] <months-value>

<day-time-literal> ::= <day-time-interval>

| <time-interval>

<day-time-interval> ::= <days-value> [<hours-value>

[:<minutes-value>[:<seconds-value>]]]

<time-interval> ::= <hours-value> [:<minutes-value>

   [:<seconds-value>]] 

   | <minutes-value> [:<seconds-value>] 

   | <seconds-value>

<years-value> ::= <datetime-value>

<months-value> ::= <datetime-value>

<days-value> ::= <datetime-value>

<hours-value> ::= <datetime-value>

<minutes-value> ::= <datetime-value>

<seconds-value> ::= <seconds-integer-value> [.[<seconds-fraction>]]

<seconds-integer-value> ::= <unsigned-integer>

<seconds-fraction> ::= <unsigned-integer>

<datetime-value> ::= <unsigned-integer>

<interval-qualifier> ::= <start-field> TO <end-field>

| <single-datetime-field>

<start-field> ::= <non-second-datetime-field>

[(<interval-leading-field-precision>)]

   <end-field> ::= <non-second-datetime-field>

   | SECOND[(<interval-fractional-seconds-precision>)]

<single-datetime-field> ::= <non-second-datetime-field>

[(<interval-leading-field-precision>)]

| SECOND[(>interval-leading-field-precision>

[, (<interval-fractional-seconds-precision>)]

<datetime-field> ::= <non-second-datetime-field> | SECOND

<non-second-datetime-field> ::= YEAR | MONTH | DAY | HOUR | MINUTE

<interval-fractional-seconds-precision> ::= <unsigned-integer>

<interval-leading-field-precision> ::= <unsigned-integer>

<quote> ::= '

<unsigned-integer> ::= digit…