Amazon Athena - Converting Timestamp to Date?

Question

Looking at the Date/Time Athena documentation, I don't see a function to do this, which surprises me. The closest I see is date_trunc('week', timestamp) but that results in something like 2017-07-09 00:00:00.000 while I would like the format to be 2017-07-09

Is there an easy function to convert a timestamp to a date?

score 32 · Accepted Answer · answered Jul 11 '18 at 18:46

32

The reason for not having a conversion function is, that this can be achieved with a type cast.

So a converting query would look like this:

select DATE(current_timestamp)

answered Jul 11 '18 at 18:46

jens walter

13,269
2
56
54

4

or `CAST(some_timestamp AS DATE)` – Piotr Findeisen Jul 13 '18 at 09:51
3

try this `select CAST(from_unixtime(1633431230) AS DATE) ;` – abhimanyu Oct 05 '21 at 10:59
Or if you want date with time: `select CAST(from_unixtime(1633431230) AS timestamp)`. – Marcin Aug 24 '22 at 02:50

score 2 · Answer 2 · answered Apr 04 '22 at 12:19

2

In case you have an ISO DateTime format you can use the following syntax:

select date(from_iso8601_timestamp(COLUMN_NAME)) FROM "TABLE_NAME";

answered Apr 04 '22 at 12:19

Mitko Keckaroski

954
1
8
12

Amazon Athena - Converting Timestamp to Date?

2 Answers2

Linked