Rounding Microseconds to Milliseconds MySQL

How is the best way to round microseconds to milliseconds in mysql?

For example, in a datetime(6) column, how do we round

2016-12-01 12:30:01.122456 to 2016-12-01 12:30:01.122

and

2016-12-01 12:30:01.122501 to 2016-12-01 12:30:01.123

Thanks

Solution

Try this query:

SELECT FROM_UNIXTIME(TO_SECONDS(dt) -
                     TO_SECONDS('1970-01-01 00:00:00') +
                     (ROUND(MICROSECOND(dt) / 1000, 0) / 1000)) AS new_dt
FROM yourTable

Here dt is your datetime column. For the sake of explanation, I will break down the above query using 2016-12-01 12:30:01.122456 as an example.

MICROSECOND(dt) = 122456
ROUND(MICROSECOND(dt) / 1000, 0) = 122

then

(ROUND(MICROSECOND(dt) / 1000, 0) / 1000)) = 0.122

So 0.122 is the number of fractional seconds in your datetime, which doesn't contain any precision in the microsecond range after rounding.

TO_SECONDS(dt) = # seconds EXCLUDING the fractional part

Finally, we take the number of seconds in dt since year 0 and subtract off the number of seconds from year 0 to the start of the epoch. This leaves us with number of seconds in dt since the start of the epoch. Then, we add the number of seconds excluding the fractional part to 0.122 to obtain the total number of seconds of the desired new datetime. At last, we use FROM_UNIXTIME() to convert this number of seconds back to a bona fide datetime.