From: Travis Ard (travis_ardhotmail.com)
Date: Wed Oct 06 2010 - 07:32:58 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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" <pascualegoria.no>
Sent: Wednesday, October 06, 2010 4:20 AM
To: "[MySQL]" <mysqllists.mysql.com>
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=travis_ardhotmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]