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
