Syntax

 

<interval literal> ::=

 

 

 

INTERVAL [ + | - ] <interval string> <interval qualifier>

 

<interval string> ::=

 

 

 

<quote> [ + | - ] { <year-month literal> | <day-time literal> } <quote>

 

<year-month literal> ::=

 

|

 

years [ -months ]

months

 

<day-time literal> ::=

 

<day-time interval> ::=

 

 

 

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

 

<time interval> ::=

 

|

|

 

hours [ :minutes [ :seconds [ . [ milliseconds ] ] ] ]

minutes [ :seconds [ . [ milliseconds ] ] ]

seconds [ . [ milliseconds ] ]

 

<interval qualifier> ::=

 

<primary datetime field> ::=

 

<non-second primary datetime field> ::=

 

|

|

|

|

|

|

|

|

|

|

|

|

 

YEAR

MONTH

DAY

HOUR

MINUTE

DAYOFYEAR

ISODAYOFWEEK

ISOWEEK

ISOMONTH

ISOYEAR

ISOWEEKOFMONTH

ISODAYOFMONTH

ISODAYOFYEAR

 

 

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.



DAYOFYEAR - day of the calendar year (1 to 365/366 starting on year-01-01)

ISODAYOFWEEK - day of week according to ISO 8601 (1 = monday to 7 = sunday)

ISOWEEK - week of year according to ISO 8601

ISOMONTH - month of year according to ISO 8601x (can be different from calendar month)

ISOYEAR - year of week according to ISO 8601 (can be different from calendar year)

ISOWEEKOFMONTH - week of month according to ISO 8601x

ISODAYOFMONTH - day of month according to ISO 8601x

ISODAYOFYEAR - day of year according to ISO 8601



 

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

 

 

The following SQL can be used to check out samples of the returned values:

 

CREATE LOCAL TEMPORARY TABLE #dates (

  dt DATE

);

 

DECLARE StartDT, EndDT, CurrentDT DATE;

 

SET StartDT = CURRENT_TIMESTAMP;

SET EndDT = StartDT + INTERVAL '5' YEAR;

 

SET CurrentDT = StartDT;

 

WHILE CurrentDT < EndDT DO

  INSERT INTO #dates (dt) VALUES (CurrentDT);

  SET CurrentDT = CurrentDT + INTERVAL '1' DAY;

END WHILE;

 

SELECT

  *,

  EXTRACT(DAYOFYEAR FROM dt) AS "DAYOFYEAR",

 

  EXTRACT(ISODAYOFWEEK FROM dt) AS "ISODAYOFWEEK",

  EXTRACT(ISOWEEK FROM dt) AS "ISOWEEK",

  EXTRACT(ISOMONTH FROM dt) AS "ISOMONTH",

  EXTRACT(ISOYEAR FROM dt) AS "ISOYEAR",

  EXTRACT(ISOWEEKOFMONTH FROM dt) AS "ISOWEEKOFMONTH",

  EXTRACT(ISODAYOFMONTH FROM dt) AS "ISODAYOFMONTH",

  EXTRACT(ISODAYOFYEAR FROM dt) AS "ISODAYOFYEAR"

FROM

  #dates; 

 


 

Conformance

 

SQL:2003 standard

-

Feature F052 "Intervals and datetime arithmetic"