Amazon Athena: Convert bigint timestamp to readable timestamp

Question

I am using Athena to query the date stored in a bigInt format. I want to convert it to a friendly timestamp.

I have tried:

    from_unixtime(timestamp DIV 1000) AS readableDate

And

     to_timestamp((timestamp::bigInt)/1000, 'MM/DD/YYYY HH24:MI:SS') at time zone 'UTC' as readableDate

I am getting errors for both. I am new to AWS. Please help!

Answer 1

Assuming you have t value representing "Java timestamp" (milliseconds since epoch), you can use from_unixtime:

from_unixtime(timestamp / 1000e0)

If you want to discard the millisecond component of your timestamp value, this will do this:

from_unixtime(timestamp / 1000)