streetlife streetlife - 1 month ago 7
SQL Question

Mysql select grouping by part of a date field

I have an application log table. I currently count number of actions per minute by using the following query

SELECT COUNT(id)
FROM application_log
WHERE activity_date LIKE '%2016-11-01 18:12%' AND activity_type = 'Full Invoice' AND status = 'Success'


Currently, I have to change each minute and rerun the query.

Is there any way I can get the count per minute for a period of one hour? Or go further by getting the number of actions per hour for a day?

Answer

Use DATE_FORMAT to get the time down to minutes, and group by that.

SELECT DATE_FORMAT(activity_date, '%H:%i') AS time, COUNT(*)
FROM application_log
WHERE activity_date BETWEEN @start_time AND @end_time
AND activity_type = 'Full Invoice' AND status = 'Success'
GROUP BY time
ORDER BY time

If the time range spans more than a day, add the date to the format string.

Comments