How to round a DateTime in MySQL?

Question

I want to discretize the DateTime with the resolution of 5 minutes. I did it in C#, but how to convert the following code to MySQL?

DateTime Floor(DateTime dateTime, TimeSpan resolution)
{
    return new DateTime
        (
             timeSpan.Ticks * 
             (long) Math.Floor
             (
                  ((double)dateTime.Ticks) / 
                  ((double)resolution.Ticks)
             )
        );
}

Answer 1

Have you had a look at the CAST functionality in MySQL?

• MySQL Cast
• Cast Functions and Operators

Answer 2

You can look here. This example is a general case for rounding to the nearest X minutes, and is written in T-SQL, but the logic and the majority of the functions will be the same in both cases.

Answer 3

It's a little nasty when you do it with datetime data types; a nice candidate for a stored function.

DATE_SUB(DATE_SUB(time, INTERVAL MOD(MINUTE(time),5) MINUTE ), 
         INTERVAL SECOND(time) SECOND)

It's easier when you use UNIXTIME timestamps but that's limited to a 1970 - 2038 date range.

FROM_UNIXTIME(UNIX_TIMESTAMP(time) - MOD(UNIX_TIMESTAMP(time),300))

Good luck.

Answer 4

from_unixtime(floor(unix_timestamp('2006-10-10 14:26:01')/(60*5))*(60*5))
+---------------------------------------------------------------------------+
| from_unixtime(floor(unix_timestamp('2006-10-10 14:26:01')/(60*5))*(60*5)) |
+---------------------------------------------------------------------------+
| 2006-10-10 14:25:00                                                       |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

you can replace the two 5s with other values