Creating table with "interval" column type

[Julien Phalip <jp...@gmail.com>]

Hi,

I'm using Hive 3.1.2 and I can't quite figure out how to define a table
with an "interval" column type.

I've tried both:

CREATE TABLE (duration INTERVAL);

and:

CREATE TABLE (duration INTERVAL DAY);

but that returns an exception:

cannot recognize input near 'INTERVAL' 'DAY' ',' in column type

Could you clarify what the proper way is?

Thank you,

Julien

Re: Creating table with "interval" column type

[Chris Nauroth <cn...@apache.org>]

Hi Julien,

Unless I'm mistaken, interval is not a full-fledged storable, persistent
type that can go into a table definition. It can only be used within DML
expressions, filters, joins, etc. For example:

SELECT CURRENT_DATE as today, CURRENT_DATE + INTERVAL '7' DAY AS next_week;

+-------------+------------------------+
|    today    |       next_week        |
+-------------+------------------------+
| 2022-11-11  | 2022-11-18 00:00:00.0  |
+-------------+------------------------+

I hope this helps.

Chris Nauroth


On Fri, Nov 11, 2022 at 11:45 AM Julien Phalip <jp...@gmail.com> wrote:

> Hi,
>
> I'm using Hive 3.1.2 and I can't quite figure out how to define a table
> with an "interval" column type.
>
> I've tried both:
>
> CREATE TABLE (duration INTERVAL);
>
> and:
>
> CREATE TABLE (duration INTERVAL DAY);
>
> but that returns an exception:
>
> cannot recognize input near 'INTERVAL' 'DAY' ',' in column type
>
> Could you clarify what the proper way is?
>
> Thank you,
>
> Julien
>