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 [EMAIL PROTECTED] |
| 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 |
+------------------------------------------------------------------------+
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
