Round up a mysql utc_timestamp to the next five minute block

I need to find the next (minutes divisible by five):00 fro开发者_运维问答m from the result of obtaining the current time in UTC, let me explain...

I use the

SELECT UTC_TIMESTAMP()

Function in MySql, which returns me "2010-11-11 16:26:19".

I then want to convert this value to "2010-11-11 16:30:00", preferably using MySql only, or a combination of MySql and PHP.

Cheers!

MsSQL:
DATEADD(second, (60 - DATEPART(second, Last_Updated)) + ((5 - (DATEPART(minute, Last_Updated) % 5)) * 60) - 60, Last_Updated)
MySQL:
SELECT DATE_ADD(UTC_TIMESTAMP(), INTERVAL ((60 - SECOND(UTC_TIMESTAMP())) + ((5 - (MINUTE(UTC_TIMESTAMP()) % 5)) * 60) - 60) SECOND);

Replace "Last_Updated" with the value you're converting, but that will found anything over 5:00 to the next 5 minute interval.

2010-10-26 18:04:07.000 2010-10-26 18:05:00.000
2010-11-10 17:18:59.000 2010-11-10 17:20:00.000
2010-11-10 20:59:27.000 2010-11-10 21:00:00.000
2010-11-10 20:59:46.000 2010-11-10 21:00:00.000
2010-11-11 12:00:51.000 2010-11-11 12:05:00.000
2010-11-11 12:30:59.000 2010-11-11 12:35:00.000

Those are the results I got testing against a database on my end.

EDIT Forgot to include the MySQL version, oops. Also kept the MsSQL version incase anyone else was curious along the way.

Edit: I'm no MySQL-user... I'll leave the essentials and somone might do more with it.

SELECT UTC_TIMESTAMP() + 300 - UNIX_TIMESTAMP() % 300;

Edit: I just rewrote the above to work in MySQL:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(UTC_TIMESTAMP()) 
+ 300 - (UNIX_TIMESTAMP(UTC_TIMESTAMP()) % 300));

I don't think that's exactly what the OP wants, because it also round up by 5 minutes when the time is exactly divisible by 5 minutes. For example, 14:45:00 would be rounded up to 14:50:00, which is wrong. But it's a step in the right direction.

I would use UNIX_TIMESTAMP() and FROM_UNIXTIME() in combination with ROUND() to get there.

So nearest 5 minute boundary would be:

select now(),from_unixtime(300 * round(unix_timestamp(now())/300));

+---------------------+-------------------------------------------------------+
| now()               | from_unixtime(300 * round(unix_timestamp(now())/300)) |
+---------------------+-------------------------------------------------------+
| 2011-04-23 09:20:35 | 2011-04-23 09:20:00                                   | 
+---------------------+-------------------------------------------------------+

With unix time you're working in seconds. Nearest 10 seconds you divide by 10, round then multiply by 10.

Nearest minute - use 60 Nearest hour - use 3600

You get the idea.