User Tools

Site Tools


Sidebar

**HPL/SQL is included to Apache Hive since version 2.0** * [[home|Home]]\\ * [[why|Why HPL/SQL]]\\ * [[features|Key Features]]\\ * [[start|Get Started]]\\ * [[doc|HPL/SQL Reference]]\\ * [[download|Download]]\\ * [[new|What's New]]\\ * [[about|About]]

interval

====== Interval Expressions ====== Interval expressions allow you to add or subtract interval values to DATE and TIMESTAMP values. **Syntax**: <code language=sql> [INTERVAL] expression DAYS | DAY | MICROSECONDS | MICROSECOND </code> Notes: * MICROSECOND expressions are converted to millisecond expressions due to Java limitations. **Example 1**: Add 1 day to DATE and TIMESTAMP values: <code language=sql> DATE '2015-03-12' + 1 DAY; -- 2015-03-13 TIMESTAMP '2015-03-12' + 1 DAY; -- 2015-03-13 00:00:00 </code> **Example 2**: Add the result of expression to to DATE and TIMESTAMP values: <code language=sql> 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 </code> **Example 3**: Subtract a millisecond fom TIMESTAMP value (microseconds is not supported): <code language=sql> TIMESTAMP '2015-03-12 10:10:10.000' - 1 MICROSECOND; /* Treated as millisecond */ -- 2015-03-12 10:10:09.999 </code> **Example 4**: Subtract multiple interval fields: <code language=sql> TIMESTAMP '2015-03-12' - 1 DAY - 1 MICROSECOND; -- 2015-03-10 23:59:59 </code> **Example 5**: Using INTERVAL keyword: <code language=sql> date '2016-01-27' - interval '3' day; -- 2016-01-24 </code> **Compatibility:** IBM DB2, Oracle, Teradata **Version**: * INTERVAL keyword - HPL/SQL 0.3.17 * Introduced - HPL/HQL 0.3 See also: * [[date-literal|DATE Literal]] * [[timestamp-literal|TIMESTAMP Literal]]