How to retrieve rows between hours

I want to get all rows from my table, the table have one column with the following date type ’14/07/2017 05:01:35 p.m.’ between specific hours. Like between ’00:01:00 am’ and ’01:00:00 am’.

I’m running Oracle Database 11g Release 2

select ID, CREATION_TIME 
from my_table 
where ID = 4 and 
      CREATION_TIME between to_date('29/04/2017 12:01:00', 'DD/MM/YYYY HH:MI:SS[AM]') and to_date('29/04/2019 01:00:00', 'DD/MM/YYYY HH:MI:SS[AM]')
order by creation_time asc;

I want to select hours instead of dates.

Answer

If you mean (from your last comment) that you want to get all records that are between the dates of Jan 1st 2019 and Apr 4th 2019, but only if the time portion of the recod is between 12:01AM and 1:00AM, then you could try this:

select ID, CREATION_TIME 
from my_table 
where ID = 4 
and CREATION_TIME between to_date('01/01/2017', 'DD/MM/YYYY') 
    and to_date('04/04/2019', 'DD/MM/YYYY')
and CREATION_TIME BETWEEN TO_DATE(TO_CHAR(CREATION_TIME, 'MM/DD/YYYY') || ' 00:01:00', 'MM/DD/YYYY HH24:MI:SS')
                AND
                TO_DATE(TO_CHAR(CREATION_TIME, 'MM/DD/YYYY') || ' 01:00:00', 'MM/DD/YYYY HH24:MI:SS')
order by creation_time asc;