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

And his fantastic function is below!

DELIMITER //;

CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END//

So to run the query, you'd basically call it with the following 

CALL make_intervals(('2011-02-23 10:00:00' - INTERVAL 5 HOUR),('2011-02-23 10:00:00' + INTERVAL 6 HOUR),1,'HOUR');
SELECT * FROM time_intervals;

So why would you do this? Depending on what you're working with, you could start to produce results for a month worth of sales for example, but rather than have program fill in the blanks for all the days that are missing and have no sales, join the sales (and allow NULL or 0 results from these sales) to this temporary table.

In my use for a mate's horrible exercise, the data he was using was simply... "holely", and while we weren't in a position to fix the data we had, using this simply allowed us to pull in available data using LEFT JOIN and allowing NULLS to be returned to us.

It's far easier to have MySQL do the leg work for you, rather than to gather the data that is available (in 18 MySQL statements...) and walk through your array of results, moving only onto the next index of the array when you find acceptable data. Which needless to say, was incredibly bulky in terms of PHP, ugly and what I would define as unmanageable!

So in conclusion! Huge thanks to Ron, this function in my opinion is extremely useful, and I hope others find it as useful as I have