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*
>
|