I use this statement in Oracle SQL Developer
select to_char(time,'DD/MM/YY hh24'),count(column) as xyz from table
where to_char(time,'DD/MM/YY')>= '08/04/21'
and to_char(time,'DD/MM/YY')<= '09/04/21'
and column='xyz'
group by to_char(time,'DD/MM/YY hh24')
order by to_char(time,'DD/MM/YY hh24');
What I expect is a result/table in which the result is ordered by time in ascending order (starting with the earliest hour on 08/04/21 and ending with the latest on 09/04/21. I would expect only entries for days 08/04/21 and 09/04/21. Instead, I get a result where also other dates are included like 09/02/21 or 08/12/20.
How can I modify my query?
You are converting your native date values to strings (with two-digit years!) and then comparing those strings. The string '08/12/20' is 'less than' the string '09/04/21'.
Compare your dates with other dates, which is easier as literals:
select to_char(trunc(time, 'HH'), 'DD/MM/YY HH24'), count(column) as xyz
from table
where time >= date '2021-04-08'
and time < date '2021-04-10'
and column='xyz'
group by trunc(time, 'HH')
order by trunc(time, 'HH');
I've used trunc() to remove/zero the minute and seconds parts, which means you can then group and order by that value; and just convert to a string for display at the last moment.
I've also converted to_char(time,'DD/MM/YY')<= '09/04/21' to time < date '2021-04-10' rather than time < date '2021-04-09'as your version include all data from the 9th; which may or may not be what you intended - you might have been trying to get a single day.