List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:October 6 2010 12:32pm
Subject:Re: Constructing query to display item count based on increments of time
View as plain text  
Maybe you could use something like the following to truncate your times to 
10 minute increments before doing your GROUP BY and COUNT():

select concat(date_format(timestamp_col, '%Y-%m-%d %H:'), 
truncate(minute(timestamp_col) / 10, 0), '0') from your_table;

-Travis

--------------------------------------------------
From: "Pascual Strømsnæs" <pascual@stripped>
Sent: Wednesday, October 06, 2010 4:20 AM
To: "[MySQL]" <mysql@stripped>
Subject: Constructing query to display item count based on increments of 
time

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