Maybe you could use something like the following to truncate your times to
10 minute increments before doing your GROUP BY and COUNT():
select concat(date_format(timestamp_col, '%Y-%m-%d %H:'),
truncate(minute(timestamp_col) / 10, 0), '0') from your_table;
-Travis
--------------------------------------------------
From: "Pascual Strømsnæs" <pasc...@egoria.no>
Sent: Wednesday, October 06, 2010 4:20 AM
To: "[MySQL]" <mysql@lists.mysql.com>
Subject: Constructing query to display item count based on increments of
time
Hi!
How would one go about to construct a query that counts items within an
increment or span of time, let's say increments of 10 minutes?
Imagine a simple table where each row has a timestamp, and the query
should return the count of items occurring within the timespan of a
defined period.
Say,
09:00: 14
09:10: 31
09:20: 25
09:30: 0
09:40: 12
etc.
I have been able to get collections of item occurrence based on month and
day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m
%Y" ) eg.
I can however not seem to be able to find the solution to grouping based
on the minute increments in my example above.
Any suggestions?
--
Kind regards
Pascual Strømsnæs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
