User Tools

Site Tools


Sidebar

HPL/SQL is included to Apache Hive since version 2.0

interval

This is an old revision of the document!


Interval Expressions

Interval expressions allow you to add or subtract interval values to DATE and TIMESTAMP values.

Syntax:

[INTERVAL] expression DAYS | DAY | MICROSECONDS | MICROSECOND  

Notes:

  • MICROSECOND expressions are converted to millisecond expressions due to Java limitations.

Example 1:

Add 1 day to DATE and TIMESTAMP values:

DATE '2015-03-12' + 1 DAY;
--
2015-03-13
 
TIMESTAMP '2015-03-12' + 1 DAY;
--
2015-03-13 00:00:00

Example 2:

Add the result of expression to to DATE and TIMESTAMP values:

DATE '2015-03-12' + NVL(NULL, 3) DAYS;
--
2015-03-15
 
TIMESTAMP '2015-03-12' + NVL(NULL, 3) DAYS;
--
2015-03-15 00:00:00

Compatibility: IBM DB2

Example 3:

Subtract a millisecond fom TIMESTAMP value (microseconds is not supported):

TIMESTAMP '2015-03-12 10:10:10.000' - 1 MICROSECOND; /* Treated as millisecond */
--
2015-03-12 10:10:09.999

Example 4:

Subtract multiple interval fields:

TIMESTAMP '2015-03-12' - 1 DAY - 1 MICROSECOND;
--
2015-03-10 23:59:59

Example 5:

Using INTERVAL keyword:

date '2016-01-27' - interval '3' day;
--
2016-01-24

Version:

  • INTERVAL keyword - HPL/SQL 0.3.17
  • Introduced - HPL/HQL 0.3

See also: