Syntax

<interval literal> ::=
<interval string> ::=

<year-month literal> ::=

|

years [ -months ]

months

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

day [ space hours [ :minutes [ :seconds [.msec] ] ] ]

<time interval> ::=

|

|

hours [ :minutes [ :seconds [.msec] ] ]

minutes [ :seconds [.msec] ]

seconds [.msec]

<interval qualifier> ::=
<primary datetime field> ::=
<non-second primary datetime field> ::=

|

|

|

|

YEAR

MONTH

DAY

HOUR

MINUTE


Notes

Interval literals can only be used in datetime arithmetic.
If the interval string contains a year-month literal, then the interval qualifier shall not specify DAY, HOUR, MINUTE or SECOND. If the interval string contains a day-time literal, then the interval qualifier shall not specify YEAR or MONTH.
If TO is specified, then the start field shall be more significant than the end field, and the start field shall not specify MONTH. If the start field specifies YEAR, then the end field shall specify MONTH.
MSec support added in V3.06.


Examples

Literal Examples

CURRENT_TIMESTAMP + INTERVAL '2' YEAR

CURRENT_TIMESTAMP + INTERVAL '2-6' YEAR TO MONTH

CURRENT_TIMESTAMP + INTERVAL '6' MONTH

CURRENT_TIMESTAMP + INTERVAL '15' DAY

CURRENT_TIMESTAMP + INTERVAL '15 12' DAY TO HOUR

CURRENT_TIMESTAMP + INTERVAL '12' HOUR

CURRENT_TIMESTAMP + INTERVAL '12:25' HOUR TO MINUTE

CURRENT_TIMESTAMP + INTERVAL '12:25:30' HOUR TO SECOND

CURRENT_TIMESTAMP + INTERVAL '25' MINUTE

CURRENT_TIMESTAMP + INTERVAL '25:30' MINUTE TO SECOND

CURRENT_TIMESTAMP + INTERVAL '25:30.254' MINUTE TO SECOND

CURRENT_TIMESTAMP + INTERVAL '21.672' SECOND


Conformance

SQL:2003 standard

-

Feature F052 "Intervals and datetime arithmetic"

Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference | SQL Language Elements | Literals