Hive Interval Data Types and Conversion Examples

  • Post author:
  • Post last modified:July 19, 2019
  • Post category:BigData
  • Reading time:5 mins read

Hive supports interval types in the same way as other relational databases such as Netezza, Vertica, Oracle, etc. It accepts interval syntax with unit specifications. You have to specify the units along withe interval value. For example, INTERVAL ‘1’ DAY refers to day time. In this article, we will check Hive interval data types and its conversion examples.

Hive Interval Data Types

Hive version 1.2 and above supports interval types. Intervals of time units, Year to month intervals and Day to second intervals are available in hive version 1.2 and above. Dynamic interval types are supported in Hive version 2.2 and above.

Below are the Hive interval data types:

Supported Interval DescriptionExamples along with units
SECONDINTERVAL ‘1’ SECOND
MINUTEINTERVAL ‘1’ MINUTE
HOURINTERVAL ‘1’ HOUR
DAYINTERVAL ‘1’ DAY
MONTH INTERVAL ‘1’ MONTH
YEARINTERVAL ‘1’ YEAR
YEAR TO MONTHINTERVAL ‘1-2’ YEAR TO MONTH
DAY TO SECONDINTERVAL ‘1 2:3:4.000005’ DAY

Hive Interval Types Implementation

You can implement INTERVAL data type in a same or slightly different way compared to other databases.

  • Hive allows you to specify interval qualifiers such as YEAR, MONTH, and DAY etc.
  • It is not possible to specify the scale of a Hive interval. The precision of Hive interval types include fraction of seconds with up to 6 significant digits.
  • In Hive, interval literals must include the units like ‘1′ DAY

Create Hive Table with Interval Data Type

You cannot directly specify the interval type in CREATE TABLE statement, but you can indirectly create table with INTERVAL type.

For example, consider below example to create Hive table column with interval type.

> CREATE TABLE interval_test AS 
  SELECT INTERVAL '1' day   AS day_interval, 
         INTERVAL '1' month AS month_interval; 

-- Check Column Description
> desc test_interval;
+-----------------+----------------------+----------+--+
|    col_name     |      data_type       | comment  |
+-----------------+----------------------+----------+--+
| day_interval    | interval_day_time    |          |
| month_interval  | interval_year_month  |          |
+-----------------+----------------------+----------+--+

Related Article

Hive Interval Data Types Examples

Below are some of commonly used INTERVAL data type conversion examples.

  • Hive – Add a one day to current date time using interval types
> select current_date;
+-------------+--+
|     _c0     |
+-------------+--+
| 2019-07-17  |
+-------------+--+
1 row selected (0.195 seconds)


> select current_date + interval '1' day;
+------------------------+--+
|          _c0           |
+------------------------+--+
| 2019-07-18 00:00:00.0  |
+------------------------+--+
1 row selected (0.186 seconds)
  • Hive – Add a 10 hours to current date time using interval types
> select current_date + interval '10' hour;
+------------------------+--+
|          _c0           |
+------------------------+--+
| 2019-07-17 10:00:00.0  |
+------------------------+--+
1 row selected (0.351 seconds)
  • Hive – Add a 10 seconds to current date time using interval types
> select current_date + interval '10' second;
+------------------------+--+
|          _c0           |
+------------------------+--+
| 2019-07-17 00:00:10.0  |
+------------------------+--+
1 row selected (0.282 seconds)

Related Article

Hope this helps 🙂

Tags: ,