Oracle Group By Minute Instead of Per day

Question

I have written a query to get the data received per day.

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SELECT to_CHAR(created_date, 'yyyy/mm/dd'), status_code, COUNT(workflow_txn_id_log)
FROM workflow_txn_log
WHERE status_code = 'DOWNLOAD_ALL' AND created_date > '2021/08/11'
GROUP BY  to_CHAR(created_date, 'yyyy/mm/dd'), status_code
ORDER BY to_CHAR(created_date, 'yyyy/mm/dd');

Now I want to get data with respect to every minute ordered in Ascending Order. I have tried to change date format but nothing is working. How do I do that?

Answer 1

It sounds like you want

SELECT trunc(created_date, 'mi') created_minute, 
       status_code, 
       COUNT(workflow_txn_id_log)
  FROM workflow_txn_log
 WHERE status_code = 'DOWNLOAD_ALL' 
   AND created_date > date '2021-08-11'
GROUP BY  trunc(created_date, 'mi'), status_code
ORDER BY  trunc(created_date, 'mi');

trunc(created_date, 'mi') returns a date that is truncated to the minute (i.e. the seconds are set to 0). That means that the order by will be using date comparison semantics to sort the dates rather than string comparison semantics which is what you want. The where clause is comparing created_date against a date rather than a string so that you're not doing implicit conversions.

Answer 2

If you want to get the date as a formatted string then use TO_CHAR and include the hours and minutes:

SELECT TO_CHAR(created_date, 'yyyy/mm/dd hh24:mi'),
       status_code,
       COUNT(workflow_txn_id_log)
FROM   workflow_txn_log
WHERE  status_code  = 'DOWNLOAD_ALL'
AND    created_date > DATE '2021/08/11'
GROUP BY
       TO_CHAR(created_date, 'yyyy/mm/dd hh24:mi'),
       status_code
ORDER BY
       TO_CHAR(created_date, 'yyyy/mm/dd hh24:mi');

If you want the date as a DATE data type then TRUNCate the value to the minutes (and zero the seconds):

SELECT TRUNC(created_date, 'MI'),
       status_code,
       COUNT(workflow_txn_id_log)
FROM   workflow_txn_log
WHERE  status_code  = 'DOWNLOAD_ALL'
AND    created_date > DATE '2021/08/11'
GROUP BY
       TRUNC(created_date, 'MI'),
       status_code
ORDER BY
       TRUNC(created_date, 'MI');

Note: a DATE is a binary data type and is stored with no format. Certain client applications (i.e. SQL Developer or SQL/Plus) may chose to use the NLS_DATE_FORMAT session parameter to display the date with a format but that is functionality of the client application and not of the database and cannot be relied upon to provide consistent behaviour across different clients (i.e. the NLS_DATE_FORMAT parameter will have no effect displaying dates in Java, C#, PHP, Python, etc.).