MYSQL группирует интервал дат за последние 10 минут
У меня есть база данных с сообщениями и полем отметки времени. Я хочу выбрать счетчик для каждой минуты за последние 10 минут. Это мой текущий запрос:
SELECT date_format(timestamp, '%H:%i:%s'), COUNT(*) as count
FROM test
WHERE timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 10 MINUTE) AND NOW()
GROUP by
DATE_FORMAT(timestamp, '%i')
ORDER BY timestamp ASC
Это почти работает так, как я хочу, показывая мне счет за последние 10 минут.
Однако, поскольку я группирую по метке времени, для каждой минуты, кроме самой последней, я получаю счет из первой записи базы данных для каждой минуты, что делает мой первый и последний результат намного меньше, чем другие.
Как на этом изображении:
количество меток времени
11:25:35 6
11:26:03 11
11:27:05 11
11:28:01 12
11:29:03 12
11:30:05 10
11:31:01 12
11:32:03 12
11:33:05 11
11:34:01 12
11:35:03 5
Я ищу способ группировки по минутам, используя что-то вроде NOW () вместо моей временной метки, как в этом примере.
количество меток времени
11:25:35 11
11:26:35 12
11:27:35 11
11:28:35 11
11:29:35 11
11:30:35 11
11:31:35 11
11:32:35 11
11:33:35 11
11:34:35 11
Заранее спасибо!
Обрежьте используемые даты до минут в предложении WHERE.
Возможный дубликат SELECT / GROUP BY - отрезки времени (10 секунд, 30 секунд и т. д.)
Возможный дубликат Группировка в интервал 5 минут во временном диапазоне.
Возможный дубликат Сгруппируйте запрос mysql с интервалом в 15 минут.
Возможный дубликат Группировка даты и времени MySQL в интервалы независимо от часового пояса.
Ответы 1
Проблема в пункте WHERE. Перед агрегацией необходимо удалить лишние секунды.
Один из методов - преобразовать в секунды, разделить на 60, усечь число и умножить на 60. Следующее преобразовывает значение обратно в значение даты / времени, поэтому будет использоваться индекс для этого столбца:
SELECT date_format(timestamp, '%H:%i:%s'), COUNT(*) as count
FROM test
WHERE timestamp >= from_unixtime(floor(unix_timestamp(now()) / 60) * 60) - interval 10 minute AND
timestamp < from_unixtime(floor(unix_timestamp(now()) / 60) * 60)
GROUP BY DATE_FORMAT(timestamp, '%i')
ORDER BY timestamp ASC
Большое спасибо, что сделали это для меня! Одна небольшая ошибка в вашем запросе: from_unixtimestamp должно быть from_unixtime, а 'AND' в предложении WHERE отсутствует, тогда этот ответ идеален.