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:

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.

Interval literal examples

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…