Two people already who suggested a text-based approach vs. my numeric approach.
Analysing, my method takes a single function call per record (to_unixtime); Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3 (concate, left, date_format). Someone feel like benchmarking ? :-D On Wed, Oct 6, 2010 at 5:44 PM, Hank <hes...@gmail.com> wrote: > Here's what I came up with: > > select concat(left(DATE_FORMAT(start_time, "%Y-%m-%d %h:%i" > ),15),"0") as time, count(*) from table group by time > > -Hank > > >> > >> 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=hes...@gmail.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
