TRANSACTION_DATE is a DATE data type.

This code lists all of the event dates to the MI:SSSS.

select to_char(transaction_date,'YYYY-MON-DD HH24:MI:SSSS') as trans_date from ticket_orders;

Now I want to get counts for those dates and I get ORA-00979 not a GROUP BY function.

    select to_char(transaction_date,'YYYY-MON-DD HH24:MI:SSSS') as trans_date,
     count(*)
      from ticket_orders
       group by to_char(transaction_date,'YYYY-MON-DD HH24:MI:SSSS');

ERROR ORA-00979: not a GROUP BY expression

How do I get a count of transactions to the MI:SSSS precision?

CodePudding user response：

A DATE data type is a binary data type that is composed of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS has those components and it NEVER contains fractional seconds (that is for the TIMESTAMP data type which can have fractional seconds and/or time zone information).

If DATE type does not store fractional seconds, why does a typical row from my first query look like this 2021-FEB-25 07:58:2626

That is because you are displaying the seconds twice as you use the SS format model for seconds twice.

Your query can just be:

select to_char(transaction_date,'YYYY-MON-DD HH24:MI:SS') as trans_date,
       count(*)
from   ticket_orders
group by
       transaction_date;

db<>fiddle here

CodePudding user response：

The DATE data type does not store fractional seconds. You can reference the Oracle documentation about data types.

DATE

Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

If you want a data type that stores fractional seconds you will need to use TIMESTAMP

TIMESTAMP [(fractional_seconds_precision)]

Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is 7 or 11 bytes, depending on the precision. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.