Amazon Athena - Converting Timestamp to Date?

sql amazon-athena presto
Louis picture Louis · Jul 11, 2018 · Viewed 20.4k times · Source

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?

Answer

jens walter picture jens walter · Jul 11, 2018

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)

Related questions

Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More
AWS Athena SQL Query Error with timestamp

I have a table in AWS Athena having column named 'servertime' with the data type of timestamp. I run a query like this select * from table_name where servertime between '2018-04-01 00:00:00' and '2018-04-05 23:59:59'; It …

Read More