In Apache Hive, the interval keyword is used to get a date that is either previous to or after the provided date.
For a better understanding, look at the examples below.
Test Data:
SELECT * FROM testdate;
Result:
Date1 Date2
2021-07-08 2021-07-08 02:49:00
SELECT (date1 – INTERVAL ‘6’ DAY) FROM testdate;
Result: 2021-07-02
The above statement returned a date older than 6 days to the input date.
Some keywords and their meanings are given below.
INTERVAL ‘1-2’ YEAR TO MONTH
This is shorthand for INTERVAL ‘1’ YEAR + INTERVAL ‘2’ MONTH
INTERVAL ‘1 2:3:4.000005’ DAY
This is shorthand for INTERVAL ‘1’ DAY + INTERVAL ‘2’ HOUR + INTERVAL ‘3’ MINUTE + INTERVAL ‘4’ SECOND + INTERVAL ‘5’ NANO
Please note that the “INTERVAL” keyword is available starting with Hive 1.2.0. The query will fail for anyone using Cloudera VM 5.10 or earlier versions. From version 2.2.0 onwards, the number of days does not need to be specified in single quotes. This is included to improve query readability and portability.
Hope you find this article helpful.
Please do subscribe for more interesting updates.
Big Data and SQL 