Trying to get the full data from yesterday, NOW not helpful, CURRENT_DATE doesnt' work (Amazon Athena)

Zole72 0
Viewed 24

Since the data is refreshing several times per day, I would like to have only finished days in my query results, thus the last day should be yesterday. At the moment I'm running this query and it's not catching the data for the last day, but only for the last 24 hours:

AND to_iso8601(date_trunc('DAY', date_add('DAY', 0, NOW()))) >= event_time

I'm trying with these queries, but it's not working:

AND to_iso8601(date_trunc('DAY', date_add('DAY', -1, CURRENT_DATE)) >= event_time

AND to_iso8601(date_trunc('DAY', date_add('DAY', -1, CURRENT_DATE())) >= event_time

With this query, I'm getting the data till today-24h, 2022-09-08 10:50CET (it's 10:50h at the moment in my timezone) :

SELECT
  mc_type,
  cast(from_iso8601_timestamp(event_time) AS date) AS event_time,
  query_cost
FROM
  table
WHERE
  to_iso8601(date_trunc('DAY', date_add('DAY', 0, NOW()))) >= event_time

But I would like to get the data for the whole day yesterday, so till 2022-09-08 23:59CET

Any ideas on how should I change the query?

amazon-athena where-clause presto sql
1 Answers

I suggest to perform dates comparison instead of varchar/string one. I.e.:

WHERE
  date_trunc('DAY', now()) > from_iso8601_timestamp(event_time)
Guru Stron0
Related