Drill SQL Date / Time manipulations and other Tidbits…

Apache Drill (with MapR distribution) implements most ANSI SQL functions, however there are some nuances that you need to be aware of, to avoid frustrations. Here I’ll document them as I find, so that others may benefit.

String to Date format conversion:

This is fairly well documented here: http://drill.apache.org/docs/data-type-conversion/

For people, coming from Oracle world the following example will catch you with surprise. Please Note Drill will not give any error. Check out the 3rd and 4th columns in the code-snippet below. Format with DD (instead of dd) gives wrong month. Or probably is truncating to the year. TBD

with x as (select '2018-02-01' dt from (values(1))) select dt, to_date(dt,'yyyy-M-dd'), to_date(dt,'YYYY-M-dd'), to_date(dt,'YYYY-M-DD') from x ;  
0: jdbc:drill:zk=xxxxx:5181/drill/TIT> with x as (select '2018-02-01' dt from (values(1))) . . . . . . . . . . . . . . . . . . . . . . .> select dt, . . . . . . . . . . . . . . . . . . . . . . .> to_date(dt,'yyyy-M-dd'), . . . . . . . . . . . . . . . . . . . . . . .> to_date(dt,'YYYY-M-dd'), . . . . . . . . . . . . . . . . . . . . . . .> to_date(dt,'YYYY-M-DD') from x ; 
+-------------+-------------+-------------+-------------+ 
|     dt      |   EXPR$1    |   EXPR$2    |   EXPR$3    | 
+-------------+-------------+-------------+-------------+ 
| 2018-02-01  | 2018-02-01  | 2018-02-01  | 2018-01-01  | 
+-------------+-------------+-------------+-------------+ 
1 row selected (1.294 seconds) 
0: jdbc:drill:zk=xxxxx:5181/drill/TIT> 
------------------------------------------------------------------------------------------------------ 
with x as (select '20180201' dt from (values(1))) select dt, to_date(dt,'yyyyMdd'), to_date(dt,'YYYYMdd'), to_date(dt,'YYYYMDD') from x ;   

+-----------+-------------+-------------+-------------+ 
|    dt     |   EXPR$1    |   EXPR$2    |   EXPR$3    | 
+-----------+-------------+-------------+-------------+ 
| 20180201  | 2018-02-01  | 2018-02-01  | 2018-01-01  | 
+-----------+-------------+-------------+-------------+ 
1 row selected (1.151 seconds) 
0: jdbc:drill:zk=xxxxx:5181/drill/TIT>
 

Timestamp:

0: jdbc:drill:zk=xxxxx:5181/drill/TIT> with x as (select '20180201101304' dt
 from (values(1)))  select dt, to_date(dt,'yyyyMddHHmss'),  
to_date(dt,'YYYYMddHHmss')
to_date(dt,'YYYYMDDHHmss') from x ; 
+-----------------+-------------+-------------+-------------+ 
|       dt        |   EXPR$1    |   EXPR$2    |   EXPR$3    | 
+-----------------+-------------+-------------+-------------+ 
| 20180201101304  | 2018-02-01  | 2018-02-01  | 2018-01-11  | 
+-----------------+-------------+-------------+-------------+ 
1 row selected (1.21 seconds) 
0: jdbc:drill:zk=xxxxx:5181/drill/TIT> with x as (select '20180201101304' dt
 from (values(1)))  select dt,  to_date(dt,'yyyyMddHHmss'),  
to_date(dt,'YYYYMddHHmss'), to_date(dt,'YYYYMDDHHmss'),  
to_timestamp(dt,'yyyyMddHHmss'),  to_timestamp(dt,'YYYYMddHHmss'),  
to_timestamp(dt,'YYYYMDDHHmss') from x ; 

+-----------------+-------------+-------------+-------------+------------------------+------------------------+------------------------+ 
|       dt        |   EXPR$1    |   EXPR$2    |   EXPR$3   |         EXPR$4         |         EXPR$5         |         EXPR$6         
| +-----------------+-------------+-------------+-------------+------------------------+------------------------+------------------------+ 
| 20180201101304  | 2018-02-01  | 2018-02-01  | 2018-01-11  | 2018-02-01 10:13:04.0  | 2018-02-01 10:13:04.0  | 2018-01-11 01:30:04.0  | 
+-----------------+-------------+-------------+-------------+------------------------+------------------------+------------------------+ 
1 row selected (1.025 seconds) 0: jdbc:drill:zk=xxxxx:5181/drill/TIT> 
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s