For reporting-style stats we often need to round down ‘now’ to the nearest 5 mins. For instance, my current project requires the code to identify the current ‘rolling twenty minutes’ with such rounding. An easy way to do this that works even in MySQL v4.0 is the following.

SELECT FROM_UNIXTIME(300 * FLOOR( UNIX_TIMESTAMP()/300 ))

which gives the most recently-passed 5-min datetime, taking 13:52:50 down to 13:50:00.
Of course, if you need rounding to the nearest 15 mins then the 300 would be changed to 900.

Advertisements