List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:October 6 2010 10:30am
Subject:Re: Constructing query to display item count based on increments of time
View as plain text  
convert to unixtime, convert your interval to unixtime, creatively combine
with integer division to get a base number for each period, group by that
and count().

2010/10/6 Pascual Strømsnæs <pascual@stripped>

> 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=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