Re: Adding an interval column to a date

[Daniel Barclay <db...@maprtech.com>]

Does normal date and interval arithmetic work?

It seems (from the SQL standard) that one should be able to use:

    CAST(`datetime` AS TIMESTAMP) + 5 * INTERVAL '1' HOUR

(where that "5" can also be a general expression).

Daniel


Mehant Baid wrote:
> Currently Drill supports casting to interval data type only from varchar which is in standard ISO format, here is the link to the documentation indicating more details about the format: https://drill.apache.org/docs/date-time-and-timestamp/#intervalyear-and-intervalday. Once you are able to cast to the interval data type date_add function should work as expected. Here is an example:
>
> select timestamp_column, interval_column from dfs.`/tmp/a.json`;
> +---------------------+------------------+
> |  timestamp_column   | interval_column  |
> +---------------------+------------------+
> | 2008-2-23 10:00:00  | PT5H             |
> +---------------------+------------------+
>
> Now adding the interval to the timestamp
>
>  select date_add(cast(timestamp_column as timestamp), cast(interval_column as interval hour)) from dfs.`/tmp/a.json`;
> +------------------------+
> |         EXPR$0         |
> +------------------------+
> | 2008-02-23 15:00:00.0  |
> +------------------------+
>
> Thanks
> Mehant
>
> On 6/12/15 11:39 AM, Hao Zhu wrote:
>> Here are some examples:
>> https://drill.apache.org/docs/data-type-conversion/#casting-intervals
>>
>> So I can only find the workaround as below:
>> select columns[0],columns[1] from `test2.csv` ;
>> +----------------------+---------+
>> |        EXPR$0        | EXPR$1  |
>> +----------------------+---------+
>> | 2015-06-11 17:12:20  | 5       |
>> +----------------------+---------+
>> 1 row selected (0.081 seconds)
>>
>> select to_timestamp(columns[0],'YYYY-MM-dd HH:mm:ss') as original,
>> date_add(to_timestamp(columns[0],'YYYY-MM-dd
>> HH:mm:ss'),cast(cast(CONCAT('PT',columns[1],'H') as varchar(30)) as
>> interval HOUR)) as after from `test2.csv`;
>> +------------------------+------------------------+
>> |        original        |         after          |
>> +------------------------+------------------------+
>> | 2015-06-11 17:12:20.0  | 2015-06-11 22:12:20.0  |
>> +------------------------+------------------------+
>> 1 row selected (0.09 seconds)
>>
>> Thanks,
>> Hao
>>
>> On Fri, Jun 12, 2015 at 5:59 AM, Christopher Matta <cm...@mapr.com> wrote:
>>
>>> Just to illustrate, I have this data:
>>>
>>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select * from `datemath.json`;
>>> +----------------------+---------+
>>> |       datetime       | offset  |
>>> +----------------------+---------+
>>> | 2015-06-11 17:12:20  | 5       |
>>> +----------------------+---------+
>>> 1 row selected (0.109 seconds)
>>>
>>> I would like to add the offset column to the datetime column as hours:
>>>
>>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
>>> date_add(cast(`datetime` as timestamp), cast(cast(`offset` as int) as
>>> interval hour)) from `datemath.json`;
>>> Error: SYSTEM ERROR: java.lang.AssertionError: todo: implement syntax
>>> SPECIAL(Reinterpret(*(Reinterpret(CAST(CAST($0):INTEGER):DECIMAL(2,
>>> 0)), 3600000)))
>>>
>>> [Error Id: 9516920d-6717-4412-9f1f-cb7eb84b5407 on
>>> se-node11.se.lab:31010] (state=,code=0)
>>>
>>> Hard coding the integer “5” into the query works however:
>>>
>>> 0: jdbc:drill:zk=sen11:5181,sen12:5181> select
>>> date_add(cast(`datetime` as timestamp), cast(5 as interval hour)) from
>>> `datemath.json`;
>>> +------------------------+
>>> |         EXPR$0         |
>>> +------------------------+
>>> | 2015-06-11 22:12:20.0  |
>>> +------------------------+
>>> 1 row selected (0.156 seconds)
>>>
>>> Can anyone confirm:
>>>
>>>     1. Is this the right way to go about doing it?
>>>     2. Is this not working because (as the error says) it’s still a “todo”?
>>>
>>> Any way to work around this?
>>>
>>> Chris Mattacmatta@mapr.com
>>> 215-701-3146
>>> ​
>>>
>


-- 
Daniel Barclay
MapR Technologies