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 <arjun...@outlook.com> 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 <divya.htco...@gmail.com> > Sent: Tuesday, July 3, 2018 8:51 PM > To: user@drill.apache.org > 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 >