4

I have a database with messages and a timestamp field. I want to select the count for each minute of the past 10 minutes. This is my current query: 

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

This is almost working as I want it to, showing me the count for the past 10 minutes.

However, since I'm grouping by the timestamp, for each minute except the very last, I'm getting the count from the first database entry for each minute, making my first and last result much less than the other ones.

Like in this image :

enter image description here

timestamp count
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

What I am looking for is a way to Group by minutes using something like NOW() instead of my timestamp, like this example.

timestamp count
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

enter image description here

Thank you in advance!

LBial
  • 57
  • 6
  • https://stackoverflow.com/tags/sql/info =>Do not include images of data or code! I am not the only one, I think, that can't see any image from work place. – Daniel E. Jan 09 '19 at 10:51
  • Trim the dates used to the minute in the WHERE clause. – Kickstart Jan 09 '19 at 11:46
  • Possible duplicate of [SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)](https://stackoverflow.com/questions/3086386/select-group-by-segments-of-time-10-seconds-30-seconds-etc) – Daniel W. Jan 09 '19 at 11:51
  • Possible duplicate of [Grouping into interval of 5 minutes within a time range](https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range). – Daniel W. Jan 09 '19 at 11:52
  • Possible duplicate of [Group mysql query by 15 min intervals](https://stackoverflow.com/questions/2793994/group-mysql-query-by-15-min-intervals). – Daniel W. Jan 09 '19 at 12:10
  • Possible duplicate of [Grouping MySQL datetime into intervals irrespective of timezone](https://stackoverflow.com/questions/11789818/grouping-mysql-datetime-into-intervals-irrespective-of-timezone?noredirect=1&lq=1). – Daniel W. Jan 09 '19 at 12:11

1 Answers1

2

The problem is the WHERE clause. You need to remove the excess seconds before doing the aggregation.

One method is to convert to seconds, divide by 60, truncate the number, and multiply by 60. The following converts the value back to a date/time value, so an index on that column will be used:

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
LBial
  • 57
  • 6
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you very much, that did it for me! One small mistake in your query: from_unixtimestamp should be from_unixtime, and the 'AND' in the WHERE clause is missing, then this answer is perfect. – LBial Jan 09 '19 at 12:11