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 -

- 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

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.


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
>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
>Anyone help?
>Best wishes,

| Steve Edberg                                      sbedberg@stripped |
| University of California, Davis                          (530)754-9127 |
| Programming/Database/SysAdmin      |
| "If only life would imitate toys."                                     |
|                  - Ted Raimi, March 2002                               |
|                  - |
Rounding timesPeter Hicks9 May
Re: Rounding timesSteve Edberg10 May