Obtain count to MI:SSSS precision?

Question

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?

Answer 1

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.