Athena: select between dates and certain hours

Question

I have a database that contains a series of events and times.

I want to know how to write a query where:

cast(date_parse(date_time, ‘%y-%m-%d %h:%i:%s))

is between certain dates (ex. 1/1/22 - 1/1/23) AND hours is between (ex. 6am - 3pm)

Not sure on how to incorporate both between dates as well as specific hours of day

Answer 1

You can filter the date part with a half-open interval, then use extract to check the hour portion:

select *
from mytable
where date_time >= date '2022-01-01'
  and date_time <  date '2023-01-01'
  and extract(hour from date_time) between 6 and 14

This filters on the whole 2022 year, every day between 6 am (included) and 3 pm (excluded).