We talked a bit more. There should be a way to quote literal characters in your date/time format string. Check the Javadoc that Krystal provided.
The trick is to pass the required quotes through the shell, sqlline, and SQL to Java. I’d suggest doing a little experiment in Java to get the format string right. Then, try to figure out how to pass that string through the various layers so that the quotes are preserved down to the Java format call. - Paul > On Jun 10, 2016, at 11:24 AM, Krystal Nguyen <[email protected]> wrote: > > 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 <[email protected]> > 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* >>
