Jason Jason - 21 days ago 6
MySQL Question

Rounding a DATETIME value in MySQL to nearest minute

I have a DATETIME column in a table

t
with values like:

|toStampActual |
|-------------------|
|2014-09-09 13:00:00|
|2014-09-09 13:15:03|
|2014-09-09 13:14:55|


I need to be able to update those value to:

|toStampActual |
|-------------------|
|2014-09-09 13:00:00|
|2014-09-09 13:15:00|
|2014-09-09 13:15:00|


Basically round to nearest minute...anything greater than :30 seconds goes up, anything less goes down.

I've found this answer http://stackoverflow.com/a/19291128/99401 and changed the SQL to

SELECT TIME_FORMAT(
SEC_TO_TIME(
(TIME_TO_SEC(toStampActual) DIV 60) * 60
), '%H:%i:%s') AS rounded_time
FROM `t`


SQL Fiddle

But this only rounds down. How can I round up or down based on the seconds?

Answer

One solution (SQL Fiddle) could be:

SEC_TO_TIME(((TIME_TO_SEC(toStampActual)+30) DIV 60) * 60

Which just adds half of the 60 (value of 30) before doing the DIV.

Or, just use ROUND (SQL Fiddle)

SEC_TO_TIME((ROUND(TIME_TO_SEC(toStampActual)/60)) * 60