Full credit to Ron Savage over at Stack Exchange. He's written this fantastic function which creates a temporary table with two columns, 'interval_start' and 'interval_end'.
You call this function with a MySQL datetime ('2011-02-23 05:00:00'), and a MySQL end datetime ('2011-02-23 16:00:00'). You then specify what you want returned back to you (Seconds, Minutes, Hours, Days etc. See the function below for the full list of options).
What is returned is below will be similar;
interval_start | interval_end |
---|---|
2011-02-23 05:00:00 | 2011-02-23 05:59:59 |
2011-02-23 06:00:00 | 2011-02-23 06:59:59 |
2011-02-23 07:00:00 | 2011-02-23 07:59:59 |
2011-02-23 08:00:00 | 2011-02-23 08:59:59 |
2011-02-23 09:00:00 | 2011-02-23 09:59:59 |
2011-02-23 10:00:00 | 2011-02-23 10:59:59 |
2011-02-23 11:00:00 | 2011-02-23 11:59:59 |
2011-02-23 12:00:00 | 2011-02-23 12:59:59 |
2011-02-23 13:00:00 | 2011-02-23 13:59:59 |
2011-02-23 14:00:00 | 2011-02-23 14:59:59 |
2011-02-23 15:00:00 | 2011-02-23 15:59:59 |