CODEWITHSUNDEEP
prestoamazon-athena
shaheed313
shaheed313

Reputation: 305

Use LIKE, BETWEEN, TIMESTAMP for Athena in a Presto SQL statement?

How would I make the usage start date the current date - 2 days, and have the timestamp of the start date include between 00:00:000 and 23:59:59.999? I have to use LIKE and % since usually the timestamp includes the year, month, day but I don't want to have to insert that each time I run this.

    WHERE line_item_usage_start_date = current_date - interval '2' day
AND line_item_usage_start_date BETWEEN(LIKE(TIMESTAMP '%00:00:00.000%' and TIMESTAMP '%2020-08-25 23:59:59.999%)';

Upvotes: 0

Views: 1644

Answers (1)

Martin Traverso
Martin Traverso

Reputation: 5316

If I understand correctly, line_item_usage_start_date is of type timestamp and you want to get all rows that fall somewhere between the beginning of the day and the end of the day two days ago. In that case, there are a couple of ways to go about this:

  1. You can convert the timestamp to a date by casting and then match it against the date two days ago:
WHERE cast(line_item_usage_start_date AS DATE) = current_date - INTERVAL '2' DAY
  1. Use current_timestamp and date_trunc to compute the timestamp corresponding to the beginning of the day and constrain your results to the range between the beginning of the day 2 days ago and the beginning of the day 1 day ago:
WHERE line_item_usage_start_date >= date_trunc('day', current_timestamp - INTERVAL '2' DAY) AND 
      line_item_usage_start_date < date_trunc('day', current_timestamp - INTERVAL '1' DAY)

Upvotes: 1

Related Questions