I have a query like the following.

select some_date_col, count(*) as cnt 
from <the table>
group by some_date_col

I get something like that at the output.

13-12-2021, 6
13-12-2021, 8
13-12-2021, 9
....

How is that possible? Here some_date_col is of type Date.

CodePudding user response：

Oracle date type holds a date and time component. If the time components do not match, grouping by that value will place the same date (with different times) in different groups:

The fiddle

CREATE TABLE test ( xdate date );

INSERT INTO test VALUES (current_date);
INSERT INTO test VALUES (current_date   INTERVAL '1' MINUTE);

With the default display format:

SELECT xdate, COUNT(*) FROM test GROUP BY xdate;

Result:

Now alter the format and rerun:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MON-DD HH24:MI:SS';

SELECT xdate, COUNT(*) FROM test GROUP BY xdate;

The result

Also try this:

SELECT to_char(xdate, 'YYYY-MON-DD HH24:MI:SS') AS formatted FROM test;

Result:

and this:

SELECT to_char(xdate, 'YYYY-MON-DD HH24:MI:SS') AS formatted, COUNT(*) FROM test GROUP BY xdate;

Result:

CodePudding user response：

A DATE is a binary data-type that is composed of 7 bytes (century, year-of-century, month, day, hour, minute and second) and will always have those components.

The user interface you use to access the database can choose to display some or all of those components of the binary representation of the DATE; however, regardless of whether or not they are displayed by the UI, all the components are always stored in the database and used in comparisons in queries.

When you GROUP BY a date data-type you aggregate values that have identical values down to an accuracy of a second (regardless of the accuracy the user interface).

If you want to GROUP BY dates with the same date component but any time component then use the TRUNCate function (which returns a value with the same date component but the time component set to midnight):

SELECT TRUNC(some_date_col) AS some_date_col,
       count(*) as cnt 
FROM   <the table>
GROUP BY TRUNC(some_date_col)