Jason Jason - 1 year ago 123
MySQL Question

Rounding a DATETIME value in MySQL to nearest minute

I have a DATETIME column in a table

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

(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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download