Cloudera Impala Interval type is slightly different compared to Apache Hive interval data types. Only difference is it accept interval unit as a integer, where are in Hive it is string type. Interval type in Impala woks same way as in other relational databases such as Netezza, Vertica, Greenplum, Oracle, etc. In article, we will check more information on Impala interval data type and how to convert it.
Impala Interval Data Type
Impala interval type syntax accept unit specifications. The unit could be SECOND, HOUR, DAY, MONTH, YEAR. You have to specify the units along withe interval value. For example, INTERVAL 1 DAY refers to one day time.
Below are the Impala interval data types:
| Supported Interval Description | Examples along with units |
| SECOND | INTERVAL 1 SECOND |
| MINUTE | INTERVAL 1 MINUTE |
| HOUR | INTERVAL 1 HOUR |
| DAY | INTERVAL 1 DAY |
| MONTH | INTERVAL 1 MONTH |
| YEAR | INTERVAL 1 YEAR |
YEAR TO MONTH or DAY TO SECOND interval types are not supported in Impala. The same will work with Hive interval types.
Impala Interval Types Implementation
You can implement INTERVAL data type in a same or slightly different way compared to Hive interval type.
- Impala allows you to specify interval qualifiers or unit such as SECOND, MINUTE, HOUR, DAY, WEEK, YEAR, MONTH, etc.
- It is not possible to specify the scale of a Impala interval. The precision of Impala interval types include fraction of seconds with up to 6 significant digits.
- In Impala, interval literals must include the units like 1 DAY.
Related Article,
Create Impala Table with Interval Data Type
Unlike Hive, you cannot create impala table with interval type.
Impala Interval Data Types Examples
Below are some of commonly used INTERVAL data type conversion examples.
- Impala – Add a one day to current date time using interval types
[quickstart.cloudera:21000] > select now();
Query: select now()
...
+-------------------------------+
| now() |
+-------------------------------+
| 2019-07-19 03:41:31.460866000 |
+-------------------------------+
Fetched 1 row(s) in 0.12s[quickstart.cloudera:21000] > select now() + interval 1 day;
Query: select now() + interval 1 day
...
+-------------------------------+
| now() + interval 1 day |
+-------------------------------+
| 2019-07-20 03:42:24.822133000 |
+-------------------------------+
Fetched 1 row(s) in 0.12s- Impala – Add a 10 hours to current date time using interval types
[quickstart.cloudera:21000] > select now() + interval 10 hour;
Query: select now() + interval 10 hour
...
+-------------------------------+
| now() + interval 10 hour |
+-------------------------------+
| 2019-07-19 13:43:13.701183000 |
+-------------------------------+
Fetched 1 row(s) in 0.12s- Impala – Add a 10 seconds to current date time using interval types
[quickstart.cloudera:21000] > select now() + interval 10 second;
Query: select now() + interval 10 second
...
+-------------------------------+
| now() + interval 10 second |
+-------------------------------+
| 2019-07-19 03:47:20.559916000 |
+-------------------------------+
Fetched 1 row(s) in 0.12sHope this helps 🙂