CODEWITHSUNDEEP
sqlamazon-athena
Learner
Learner

Reputation: 493

AWS Athena - Querying a particular time period

I have a data which has two time based columns - date, hour (other columns are present). My aim is to read data for a particular time period.

Data:

date         hour
22-06-20      0
22-06-20      1
..            ..
23-06-20      23

Aim: Read data between 22-06-20 4th hour to 23-06-20 4th hour.

Query I have is:

select date, hour from mytable where
date >= date("22-06-20")
and hour >= 4;

The above would give me data from 22-06-20 4th hour to 23-06-20 23rd hour. But how to stop it with 4th hour of 23-06-20.

I have shown a hard coded example above, however what I am trying is in real time as my table gets updated hourly.

select date, hour from mytable  where
date >= date(current_timestamp - interval '25' hour)
and hour >= hour(current_timestamp - interval '25' hour)

Why I am doing -25 hours is I want last 24 hours data excluding the last hour i.e say now is 10th hour of 23-06-20. I want data from 22-06-20 9th to 23-06-20 9th. Trying to solve the above query should help me do this as well.

Upvotes: 3

Views: 14865

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Assuming your date is really a timestamp:

where date >= timestamp '2020-06-20 04:00:00' and
      date < timestamp '2020-06-23 04:00:00'

EDIT:

Oh, I see. They are in two different columns. So you can do:

where (date = '2020-06-20' and hour >= 4) or
      (date > '2020-06-20' and date < '2020-06-23') or
      (date = '2020-06-23' and hour < 4)

Upvotes: 4

Related Questions