oracle - SQL timestamp filtering based only on time

Question

I want to create a query in Oracle SQL that will grab records from a given time interval, during certain hours of the day, e.g. records between 10am to noon, in the past 10 days. I tried this, but it does not work:

select * from my_table where timestamp between
to_timestamp('2020-12-30','YYYY-MM-DD')
and
to_timestamp('2021-01-08','YYYY-MM-DD')  and
timestamp between
to_timestamp('10:00:00','HH24:MI:SS')
and
to_timestamp('12:00:00','HH24:MI:SS') 

where timestamp is of type TIMESTAMP. I have also thought of using a join, but I am struggling to find a way to filter on time of day.

Is there a way to filter using only the time, not the date, or a way to filter on time for every day in the interval?

question from:https://stackoverflow.com/questions/65625745/sql-timestamp-filtering-based-only-on-time

Answer 1

select *
  from my_table
 where timestamp between to_timestamp('2020-12-30','YYYY-MM-DD')
                     and to_timestamp('2021-01-08','YYYY-MM-DD')  
   and timestamp - trunc(timestamp) between interval '10' hour
                                        and interval '12' hour

If you don't need to include exactly noon (including no fractional seconds), you could also do

select *
  from my_table
 where timestamp between to_timestamp('2020-12-30','YYYY-MM-DD')
                     and to_timestamp('2021-01-08','YYYY-MM-DD')  
   and extract( hour from timestamp ) between 10 and 11

As an aside, I'd hope that your actual column name isn't timestamp. It's legal as a column name but it is a reserved word so you're generally much better off using a different name.