Presto DB : BIGINT or LONG to TIMESTAMP

Timestamp stored in the hive column UPDT_DT in the table like,

$ presto-cli –catalog hive –schema default

presto:default> select updt_dt from HIVE_SRP_TEST_TBL limit 5;

    updt_dt    

—————

1497961733000

1497961733000

1497961733000

1497961733000

1497961733000

(5 rows)

ISSUE : When you simple convert to timestamp, the output would be like,

presto:default> select from_unixtime(updt_dt)updt_dt from HIVE_SRP_TEST_TBL limit 5;

          updt_dt           

—————————

+49438-07-09 00:00:00.000

+49438-07-09 00:00:00.000

+49438-07-09 00:00:00.000

+49438-07-09 00:00:00.000

+49438-07-09 00:00:00.000

(5 rows)

Solution :

presto:default> select from_unixtime(updt_dt / 1000) + parse_duration(cast((updt_dt % 1000) as varchar) || ‘ms’) updt_dt fromHIVE_SRP_TEST_TBL limit 5;

         updt_dt          

————————-

2017-06-20 12:28:51.000

2017-06-20 12:28:51.000

2017-06-20 12:28:51.000

2017-06-20 12:28:51.000

2017-06-20 12:28:51.000

(5 rows)