CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athenapresto
noobeerp
noobeerp

Reputation: 427

Amazon Athena: Convert bigint timestamp to readable timestamp

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!

Upvotes: 18

Views: 29460

Answers (1)

Piotr Findeisen
Piotr Findeisen

Reputation: 20760

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)

Upvotes: 32

Related Questions