Hi Divya, TO_TIMESTAMP takes varchar (string) input to convert to timestamps, not numbers. https://drill.apache.org/docs/data-type-conversion/#to_timestamp
Thanks, Chris On Wed, Jul 4, 2018 at 2:57 AM Divya Gehlot <[email protected]> wrote: > Hi , > But Why I get the timestamp in below format > > 2018-07-03T07:00:00.049-05:00 > > while you get in 2018-07-03 00:00:00.0 > do I need to make any config changes for it ? > > Why the Query 3 still gives me error > select TO_TIMESTAMP(1530601200049/1000,'YYYY-MM-dd HH:mm:ss') from > (values(1)); > Apache Drill > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > IllegalArgumentException: Invalid format: "1530601200" is malformed at "0" > [Error Id: ca3236d5-e99b-4a72-a915-8d4fe64afee6 on <node1>:31010] > > Doesn't it works this way ? > > Thanks, > Divya > > On Wed, 4 Jul 2018 at 02:30, Arjun kr <[email protected]> wrote: > > > TO_TIMESTAMP function accepts epoch timestamp in seconds. Whereas cast to > > Timestamp seems to be expecting value in milliseconds. > > > > > > > > 0: jdbc:drill:> select TO_TIMESTAMP(1530601200049/1000) from (values(1)); > > +------------------------+ > > | EXPR$0 | > > +------------------------+ > > | 2018-07-03 00:00:00.0 | > > +------------------------+ > > 1 row selected (0.297 seconds) > > 0: jdbc:drill:> > > > > Thanks, > > > > Arjun > > > > ________________________________ > > From: Divya Gehlot <[email protected]> > > Sent: Tuesday, July 3, 2018 8:51 PM > > To: [email protected] > > Subject: difference between cast as timestamp and to_timestamp > > > > Hi, > > Below gives me different values > > Query 1: > > select CAST(1530601200049 AS TimeStamp) from (values(1)); > > EXPR$0 > > 2018-07-03T07:00:00.049-05:00 > > > > Query 2: > > select TO_TIMESTAMP(1530601200049) from (values(1)); > > Apache Drill > > 50472-10-26T11:00:49.000-05:00 > > > > Query 3 : > > select TO_TIMESTAMP(1530601200049,'YYYY-MM-dd HH:mm:ss') from > (values(1)); > > Error : > > Apache Drill > > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > > IllegalArgumentException: Invalid format: "1530601200049" is malformed at > > "0049" [Error Id: 03ac470f-e959-41c4-beba-5f3827815709 on <node1>:31010] > > > > Why get I different values? > > > > Thanks, > > Divya > > >
