Rounding a DATETIME value in MySQL to nearest minute

mysql round datetime to hour
round timestamp to nearest minute
sql round timestamp to nearest minute
mysql convert datetime to date
mysql time format
mysql convert date to string
mysql time format am/pm

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?

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)

MySQL - Rounding TIME to nearest minute, 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  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

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?

How to Round Timestamps in MySQL, We want to charge someone per minute to the nearest minute. mysql> insert into timer (starttime, endtime) values (time_format('09:00:00', '%H:%i:%s'),  Rounding In the case of rounding up whether it is to the nearest minute or the nearest hour this can be achieved by doing the following calculation: CEIL(difference / 60) * 60 The CEIL effectively rounds up the result of difference divided by 60 resulting in an INTEGER being returned.

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

MYSQL Date Time Round To Nearest Hour, Rounding or truncating timestamps are especially useful when you're grouping by time. If you are rounding by year or date, you can use the corresponding functions: Value to a Column in MySQL How to Add a Not Null Constraint in MySQL hour SELECT date_format(now(),'%Y-%m-%d %H:%i'); -- round to the minute. MySQL Forums Forum List I need to round a datetime field to the closer quarter of hour. Example 2006-10-10 14:15:01 // it must return 2006-10-10 14:30:00

MySQL 5.6 Reference Manual :: 12.6 Date and Time , DATE_ADD: This adds the number of hours for the round into the result. Questions: Answers: Half of the hour is a 30 minutes. Simply add 30  If you are at 19:20, it will add 30 to 19:50 and you get 19:00. If you are at 19:31 it will add the 30 to bring you to 20:01 which truncates to 20:00. You can do the same thing with days but instead add 12 hours to the time then truncate. Minute rounding would add 30 seconds then truncate.

Solved: Rounding a date/time field to the nearest half hou, Functions that expect date values usually accept datetime values and ignore the or if the first argument is a DATE and the unit value uses HOURS , MINUTES  Rounding to hours / minutes is as easy as changing the @precision…: @precision = 60*60; — round to hours @precision = 60; — round to minutes @precision = 1; — round to seconds. But can also do: @precision = 15*60; — round to 15 minutes @precision = 5; — round to 5 seconds @precision = 2.5*60*60; — round to 2.5 hours

Oracle ROUND (date) function, Solved: We have a date/time field that I'd like to accomplish the Round the time down to the nearest half hour increment (for instance, 8:43 would It looks like if it goes over the 15 or 45 minute mark it rounds up - I still need  Well, not earlier, but it is here today about how to create a function to round up time to nearest minute interval. Here is a very simple example of how one can do the same. CREATE FUNCTION [dbo].[RoundTime] (@Time DATETIME, @RoundToMin INT) RETURNS DATETIME AS BEGIN RETURN ROUND(CAST(CAST(CONVERT(VARCHAR, @Time,121) AS DATETIME) AS FLOAT

Comments
  • 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.
  • does this work with datetime? dev.mysql.com/doc/refman/5.5/en/… "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.
  • 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.