Time Rounding

Hi guys,

I have a time stored in a mysql db in hh:mm format, how would i round that to the nearest 15 mins.

for example :-
10:13 -> 10:15
06:08 -> 06:15
02:41 -> 02:45


Can i use the Up / Down as buttons to increase / decrease the hours to pay field by 15 mins using PHP?

Thanks in advance…

Personally I’d store the true time in the database - you never know when having that will be useful. I’d then look to see if there was an existing MySQL function to do this rounding and if not I’d write a MySQL function to do it.

The reason for this approach is I want my view code to be able to request data in the format it needs, and the model’s job is that preparation since multiple distinct views might request the same format (format here in the sense of phrasing, not in the sense of CSS markup or HTML which doesn’t go in the model)

Appreciate the reply, although i must apologise for not explaining clearly. however i want to keep the ‘actual time’ in the DB for the same reasons as you have given. All im trying to do is display the time rounded up/down.

As for writing a function… that’s the bit i don’t know how to do.

Stolen shamelessly from the mySQL forums, and modified slightly.

SELECT from_unixtime(round(unix_timestamp(`datetimefield`)/900)*900) FROM `table` ORDER BY `datetimefield`; 

(Translation: Take the datetime, convert to timestamp, divide by 15 minutes, round it, multiply by 15 minutes, and reconstruct it into a datetime)