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 <[email protected]> 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 [email protected]
> 215-701-3146
>
>
