List:General Discussion« Previous MessageNext Message »
From:Hank Date:October 6 2010 9:15pm
Subject:Re: Constructing query to display item count based on increments of time
View as plain text  
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 <vegivamp@stripped> 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 <heskin@stripped> 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=1
>> >>
>> >>
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>> >
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
Thread
Constructing query to display item count based on increments of timePascual Strømsnæs6 Oct
  • Re: Constructing query to display item count based on increments of timeJohan De Meersman6 Oct
  • Re: Constructing query to display item count based on increments of timeTravis Ard6 Oct
    • Re: Constructing query to display item count based on increments of timeHank6 Oct
      • Re: Constructing query to display item count based on increments of timeJohan De Meersman6 Oct
        • Re: Constructing query to display item count based on increments of timeHank6 Oct
          • Re: Constructing query to display item count based on increments of timeJohan De Meersman7 Oct
  • my.iniElim PDT7 Oct
    • RE: my.iniandrew.2.moore7 Oct
      • Re: my.iniElim PDT7 Oct
      • where clauseElim PDT3 Jan