CODEWITHSUNDEEP
sqloracle-sqldeveloper
Tobitor
Tobitor

Reputation: 1508

Oracle SQL: How to modify query in order to get only results within a certain timeframe?

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?

Upvotes: 0

Views: 64

Answers (2)

Alex Poole
Alex Poole

Reputation: 191435

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.

db<>fiddle demo

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231781

Assuming that time is of data type date, you don't want to do a to_char on it in your where clause or in your order by. As written, you're doing string comparisons rather than date comparisons so you're getting rows where the to_char(time string sorts alphabetically between the two values not rows where the date is between the two dates. Compare against date literals or do explicit to_date calls on your string literals

My wager is that you really want something like this

select trunc(time, 'HH24'),count(column) as xyz 
  from table
 where time >= date '2021-08-04'
   and time <= date '2021-09-04'
   and column='xyz'
 group by trunc(time, 'HH24')
 order by trunc(time, 'HH24');

Upvotes: 2

Related Questions