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 <[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
--
Daniel Barclay
MapR Technologies
