Filter rows by range dates and only get the rows in a range of hours

Question

I want you to filter the rows between a date range but I only want you to return the rows of a time range of each of the days included in the previous date range.

For example, you would like to filter out all the rows between March 1 and March 3, 2020 and also only those between 14:00 and 18:00.

So far I can filter between two dates or between two hours but it doesn't work when I put the two things together. I don't get any records.

and BEGIN_INTERVAL_TIME BETWEEN to_date('19/04/2020', 'DD/MM/YYYY') AND to_date('20/04/2020','DD/MM/YYYY')
and BEGIN_INTERVAL_TIME BETWEEN to_date('15:00:00', 'HH24:MI:SS') AND to_date('16:00:00','HH24:MI:SS')

Regards,

Answer 1

SQL> select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot
     where begin_interval_time between date'2020-04-19' and date'2020-04-22'
     and begin_interval_time between trunc(begin_interval_time, 'DD') + 14/24 and trunc(begin_interval_time, 'DD') + 18/24
     order by 1;

   SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- -------------------------
        68 19-APR-20 02.00.54.771 PM 19-APR-20 03.00.57.674 PM
        69 19-APR-20 03.00.57.674 PM 19-APR-20 04.00.00.561 PM
        70 19-APR-20 04.00.00.561 PM 19-APR-20 05.00.03.472 PM
        71 19-APR-20 05.00.03.472 PM 19-APR-20 06.00.06.333 PM
        92 20-APR-20 02.00.04.468 PM 20-APR-20 03.00.07.303 PM
        93 20-APR-20 03.00.07.303 PM 20-APR-20 04.00.10.168 PM
        94 20-APR-20 04.00.10.168 PM 20-APR-20 05.00.13.094 PM
        95 20-APR-20 05.00.13.094 PM 20-APR-20 06.00.15.965 PM
       116 21-APR-20 02.00.14.249 PM 21-APR-20 03.00.17.113 PM
       117 21-APR-20 03.00.17.113 PM 21-APR-20 04.00.19.990 PM
       118 21-APR-20 04.00.19.990 PM 21-APR-20 05.00.22.928 PM
       119 21-APR-20 05.00.22.928 PM 21-APR-20 06.00.25.839 PM

12 rows selected.