"group by day" in Oracle doesn't appear to group by date

Question

My query looks something like this:

select datesent, count(*) the_count from receivedmessaged where status=5000
and datesent>(to_date('20130101', 'YYYYMMDD')) group by datesent

What I'm looking for is a table that has the count of messages with a status of 5000 per day, newer than a certain date. What I'm getting is a table with the same dates over and over. What I think is happening is that there is a hidden time part in that datesent field, and its grouping the entries by the exact time they were sent, rather than just looking at the date. Can anyone confirm this and tell me how I can fix it? Thanks!

Answer 1

What I think is happening is that there is a hidden time part in that datesent field, and its grouping the entries by the exact time they were sent, rather than just looking at the date.

That's very probably what's happening. So try that:

select TRUNC(datesent), count(*) the_count from receivedmessaged where status=5000
and datesent>(to_date('20130101', 'YYYYMMDD')) group by TRUNC(datesent)

TRUNC will remove the "time part" and allow you to group by day.

---

Please note that the use of TRUNC wil invalidate your index. Take a look at your execution plan. And if needed, you should add a function-based index on TRUNC(datesend).

Answer 2

Of course, using TRUNC would solve your issue, and using a function-based index would make it efficient.

However, from 11g onwards, you could also use VIRTUAL colums. In your case, you can add a virtual column as new_date ALWAYS GENERATED AS (TRUNC(date_column)). You just need to use this virtual column in your query. For performance improvement, if required, you could create an index.

NOTE : Indexes defined against virtual columns are equivalent to function-based indexes.