Invalid and mismatched count

When I run below query:

select trunc(create_date), count(*) as num_created
from mytable WHERE create_date >= (sysdate -9)
group by trunc(create_date) order by 1;

The output:

06-MAR-24   7
07-MAR-24   1826
08-MAR-24   1135
09-MAR-24   1253
10-MAR-24   1119
11-MAR-24   1446
12-MAR-24   1400
13-MAR-24   2498
14-MAR-24   1633
15-MAR-24   616

And when I run below query:

select trunc(create_date), count(*) as num_created
from mytable WHERE create_date >= (sysdate -10)
group by trunc(create_date) order by 1;

The output:

05-MAR-24   165
06-MAR-24   1020
07-MAR-24   1826
08-MAR-24   1135
09-MAR-24   1253
10-MAR-24   1119
11-MAR-24   1446
12-MAR-24   1400
13-MAR-24   2498
14-MAR-24   1633
15-MAR-24   616

Wondering why the count for 06 March becomes 1020 from 7. (and so on) What I am missing here or doing wrong?

>Solution :

In Oracle, a DATE is a binary data-type that consists of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS contains those 7 components and it is NEVER stored in a human readable format.

When you use SYSDATE - 10 then you are subtracting 10 days from the current instant and it will have the same time component so:

SELECT SYSDATE,
       SYSDATE - 10,
       TRUNC(SYSDATE - 10)
FROM   DUAL

Outputs:

and:

WHERE create_date >= sysdate - 10

will get all the results that are in the last 10 days from the current instant; which means that you will get a partial day’s results.

If you want a full day then use TRUNC to truncate the time-component to midnight:

WHERE create_date >= TRUNC(sysdate -10)

fiddle