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)

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s