Ok, I can see that. Here's a different approach that gets it down to
two function calls and some math.. and the DATE_FORMAT call might not
even be needed depending on the actual application.
select
DATE_FORMAT(start_time, "%Y-%m-%d %h:" ) as dhour,
10*(minute(start_time)%6) as dtime ,count(*)
from table
group by dhour,dtime;
-Hank
On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman <vegiv...@tuxera.be> wrote:
> 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
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
