List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 3 2001 8:38pm
Subject:Re: time grouping in mysql
View as plain text  
At 9:02 AM -0500 11/3/01, Greg Sarsons wrote:
>I haven't succeeded in being able to group by time.  My timestamp field
>is yyyy-mm-dd hh:mm:ss.  What I would like is have a query that will
>enable me to be to group on events say every 5 minutes.
>
>I've played around with using time_to_sec(timestamp) to convert all the
>times to seconds and then group by the timestamp to return only distinct
>seconds (timestamp).  the timestamp field is multivalued.

Convert to seconds as you're doing, then divide the result by 300 (5 minutes
in seconds) to slot them into 5-minute bins.

You'll probably want TRUNCATE(seconds_val/300,0) to produce integer
division.  Otherwise, you'll get a bunch of floating point values
that don't group as you want.


>
>Any thoughts
>
>Greg

Thread
time grouping in mysqlGreg Sarsons3 Nov
  • Re: time grouping in mysqlPaul DuBois3 Nov