CODEWITHSUNDEEP
sqlhiveimpala
Anu Dahiya
Anu Dahiya

Reputation: 21

Microseconds to timestamp impala

I'm trying to convert microseconds to timestamp in Impala.

I've tried the following but need format "YYYY-MM-DD HH:MM:SS":

select TIME_JIFFY,
    (cast(TIME_JIFFY as bigint) - 4*60*60*65536) as jiffy_in_est,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) as no_of_days,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) as jiffy_hours,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - (((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536) + ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) * 60 * 60 * 65536))) / 65536 / 60)) as jiffy_minutes,
    (floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - (((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536) + ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) * 60 * 60 * 65536) + ((Floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - (((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536) + ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) - ((floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24))) * 24 * 60 * 60 * 65536)) / 65536 / 60 / 60)) * 60 * 60 * 65536))) / 65536 / 60)) * 60 * 65536))) / 65536)) as jiffy_seconds,
    (to_date(days_add(to_date(to_utc_timestamp('1980-01-01','EST5EDT')),(floor(((cast(TIME_JIFFY as bigint) - 4*60*60*65536) / 65536 / 60 / 60 / 24)))))) as emni
From gg.table_name

I'm getting something like below: enter image description here

Upvotes: 1

Views: 866

Answers (1)

Oleksandr Yarushevskyi
Oleksandr Yarushevskyi

Reputation: 3299

You need to convert microseconds to Unix time (seconds, bigint), then you can use build-in function from_unixtime to get the format "YYYY-MM-DD HH:MM:SS":

select from_unixtime(CAST(TIME_JIFFY / 1000000 AS BIGINT),"yyyy-MM-dd HH:mm:ss");

+----------------------------------------------------------------------------------+
| from_unixtime(cast(TIME_JIFFY / 1000000 as bigint), 'yyyy-mm-dd hh:mm:ss') |
+----------------------------------------------------------------------------------+
| 2014-02-14 16:21:01                                                              |
+----------------------------------------------------------------------------------+

Upvotes: 1

Related Questions