Rounding a DATETIME value in MySQL to nearest minute

Question

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 https://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?

Try: `SELECT TIME_FORMAT(SEC_TO_TIME(((TIME_TO_SEC(toStampActual) +30) DIV 60) * 60), '%H:%i:%s') AS rounded_time FROM 't'`, see [SQL Fiddle](http://sqlfiddle.com/#!2/e112a/1/0). — lurker, Sep 09 '14 at 14:49

lurker · Accepted Answer · 2018-12-17T10:58:27.417

8

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)

edited Dec 17 '18 at 10:58

answered Sep 09 '14 at 14:57

lurker

56,987
9
69
103

does this work with datetime? https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sec-to-time "Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value. The range of the result is constrained to that of the TIME data type." I used FROM_UNIXTIME and UNIX_TIMESTAMP instead because of this. – inarilo May 14 '17 at 09:47
The parenthesis aren't correct in either example. There's four left parens but only three right parens. Add one more right paren on the right side. – wojtow Dec 17 '18 at 03:22

score 0 · Answer 2 · edited Sep 13 '18 at 10:33

Example of rounded to 5 minutes

If yours filed is only TIME for example 01:41:00
the filed that contain the time called fit.avergeTime

First step:
We need to know the last character that contain the minutes, but only the single minutes, for example, if the time is 01:41:00, we want to get only the minute 1 and not 41 we get that result by

SUBSTRING(fit.avergeTime, 5, 1)

then we use the case / when and add the correct numbers of minutes

case SUBSTRING(fit.avergeTime, 5, 1) 
    when 0 then fit.avergeTime 
    when 1 then DATE_ADD(fit.avergeTime, INTERVAL 4 MINUTE) 
    when 2 then DATE_ADD(fit.avergeTime, INTERVAL 3 MINUTE) 
    when 3 then DATE_ADD(fit.avergeTime, INTERVAL 2 MINUTE) 
    when 4 then DATE_ADD(fit.avergeTime, INTERVAL 1 MINUTE) 
    when 5 then fit.avergeTime 
    when 6 then DATE_ADD(fit.avergeTime, INTERVAL 4 MINUTE) 
    when 7 then DATE_ADD(fit.avergeTime, INTERVAL 3 MINUTE) 
    when 8 then DATE_ADD(fit.avergeTime, INTERVAL 2 MINUTE) 
    when 9 then DATE_ADD(fit.avergeTime, INTERVAL 1 MINUTE)

Hope this helps?

score -5 · Answer 3 · edited Nov 18 '16 at 12:29

update yourtable set new_start_time = CASE substring(new_start_time,5,1)+0
WHEN 0 THEN  addtime(new_start_time,'00:00') 
WHEN 1 THEN addtime(new_start_time,'-00:01') 
WHEN 2 THEN addtime(new_start_time,'-00:02') 
WHEN 3 THEN  addtime(new_start_time,'00:02') 
WHEN 4 THEN  addtime(new_start_time,'00:01') 
WHEN 5 THEN  addtime(new_start_time,'00:00') 
WHEN 6 THEN  addtime(new_start_time,'-00:01') 
WHEN 7 THEN  addtime(new_start_time,'-00:02') 
WHEN 8 THEN  addtime(new_start_time,'00:02') 
WHEN 9 THEN  addtime(new_start_time,'00:01') END

Rounding a DATETIME value in MySQL to nearest minute

3 Answers3

Linked

Related