CODEWITHSUNDEEP
mysqldatetime
Jason
Jason

Reputation: 15335

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 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?

Upvotes: 5

Views: 17336

Answers (3)

lurker
lurker

Reputation: 58224

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)

Upvotes: 9

Yoni Ayalon
Yoni Ayalon

Reputation: 507

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?

Upvotes: 0

V R K RAO
V R K RAO

Reputation: 111

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

Upvotes: -5

Related Questions