Presto TIMESTAMP get data from 2 days ago without inputting year month date?

1
votes

My goal is to have the query grab data from 2 days ago. I don't want to have to keep inputting the date like this:

WHERE usage_start_date
BETWEEN TIMESTAMP '2020-09-09 00:00:00.000' and TIMESTAMP '2020-09-09 
23:59:59.999'

but instead something like:

 usage_start_date = current_date - interval '2' day

the above works for my Athena Presto SQL query, but for some reason will not give all the data that ran in those 24 hours, instead giving about half the day. Is there a way to do a statement like this one to ensure it gives ALL data in that day?

 WHERE current_date - interval '2' day AND
BETWEEN TIMESTAMP '00:00:00.000' and TIMESTAMP '23:59:59.999'

without inputting the year, month, day? It seems like TIMESTAMP needs the y/m/d but what about doing a LIKE so it picks up the hour, minute, second but no need to put the y/m/d?

3
sqldatetimestampprestoamazon-athena
can you try select * from table where hour(usage_start_date) between 0 and 23 and current_date - interval '2' day = date(usage_start_date) and let me know if this works for your use case? - Prabhakar Reddy
What's the data type of usage_start_date? - Martin Traverso
@PrabhakarReddy this worked! thank you...please submit as answer so i can choose as best answer - shaheed313
@MartinTraverso string - shaheed313

3 Answers

3
votes

To get a timestamp for the start of the day that was two days ago you can do

DATE_TRUNC('day', NOW() - INTERVAL '2' DAY)

e.g.

WHERE usage_start_date >= DATE_TRUNC('day', NOW() - INTERVAL '2' DAY)
  AND usage_start_date <  DATE_TRUNC('day', NOW() - INTERVAL '1' DAY)
0
votes

You can use below query to achieve the task by fetching the hour and date from the usage_start_date

select * from table where hour(usage_start_date) between 0 and 23 and current_date - interval '2' day = date(usage_start_date)
0
votes

I would suggest:

WHERE usage_start_date >= CURRENT_DATE - INTERVAL '2' DAY AND
      usage_start_date < CURRENT_DATE - INTERVAL '1' DAY