List:General Discussion« Previous MessageNext Message »
From:Hank Date:October 6 2010 3:44pm
Subject:Re: Constructing query to display item count based on increments of time
View as plain text  
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
>
>
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