drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Krystal Nguyen <kngu...@maprtech.com>
Subject Re: Date-Time Conversion
Date Fri, 10 Jun 2016 18:24:21 GMT
Here is the spec for the DateTimeFormat class the drill uses:
http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

I created a table with the date time data you provided:

select * from `dfs.tmp`.`test.csv`;
+----------------------------------------------+
|                   columns                    |
+----------------------------------------------+
| ["Thu Jun 09 2016 17:00:25 GMT+0530 (IST)"]  |
+----------------------------------------------+

Joda time is unable to parse the parenthesis around the timezone at the end
so I used substring function with regex to extract the data before the
parenthesis.

select to_timestamp(substring(columns[0] from '^.*0'),'E MMM dd YYYY
HH:mm:ss zZ') from dfs.tmp.`test.csv`;
+------------------------+
|         EXPR$0         |
+------------------------+
| 2016-06-09 11:30:25.0  |
+------------------------+

Thanks,
Krystal

On Fri, Jun 10, 2016 at 3:24 AM, Anup Tiwari <anup.tiwari@games24x7.com>
wrote:

> Hi All,
>
> We have a column in table in which date time is coming in below format :-
>
> Thu Jun 09 2016 17:00:25 GMT+0530 (IST)
>
> We want to extract date-time in "yyyy-MM-dd hh:mm:ss" (2016-06-09
> 17:00:25") format.
>
> As far as my knowledge their is no in build function to achieve this.
> Kindly let me know how to do it.
>
>
> Regards,
> *Anup*
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message