List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:May 10 2002 2:16am
Subject:Re: Rounding times
View as plain text  
Well, assuming the time is in seconds (as in a UNix timestamp), you 
could use rounding function:

	select 600 * round(time_in_seconds/600)

(600 being # of seconds in 10 minutes).

Read the docs for the round() function -

	http://www.mysql.com/doc/M/a/Mathematical_functions.html

- for some caveats in round behavior. If your system doesn't round 
the way you want, you'll have to get a bit more complex:

	select 600 * floor((time_in_seconds+300)/600)

This will round 00:05:00, for example up to 00:10:00; use 299 instead 
of 300 if you want to round down.

To do conversions to/from various date & time formats, see

	http://www.mysql.com/doc/D/a/Date_and_time_functions.html

Standard warnings about off-top-of-head untested code apply.

Also, depending on the format of your database, you might be able to 
group your data using date_add()/date_sub() functions and INTERVAL 
operator. See the date & time functions link above.

	-steve



At 2:33 PM +0100 5/9/02, Peter Hicks wrote:
>Hi everyone
>
>I have a time value in MySQL that I want to round to the nearest ten minutes -
>for example, 00:32:15 should round to 00:30:00, and 00:48:05 should round to
>00:50:00.
>
>I have racked my brains over this, and I can't work out how to do this. I'm
>attempting to write a query which will create ten-minute summaries from a
>data-set.
>
>Anyone help?
>
>Best wishes,
>
>Peter.
>


-- 
+------------------------------------------------------------------------+
| Steve Edberg                                      sbedberg@stripped |
| University of California, Davis                          (530)754-9127 |
| Programming/Database/SysAdmin               http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| "If only life would imitate toys."                                     |
|                  - Ted Raimi, March 2002                               |
|                  - http://www.whoosh.org/issue67/friends67a.html#raimi |
+------------------------------------------------------------------------+
Thread
Rounding timesPeter Hicks9 May
Re: Rounding timesSteve Edberg10 May