Q:

Return actual hours of day between two times

I have a rather interesting problem at the minute where i want to return the actual hour values between two times. I dont mind if this is either in c# or mysql but i'm not sure what way to go about it.
Eg Time 1= 13:00   & Time 2= 18:10
i would like to return  13:00,14:00,15:00,16:00,17:00,18:00
There is plenty around for calculating the hour count between two times and i know i can use that integer value and increment my base hour as below but i was wondering if there was a cleaner way?
Mysql
timediff('2014-04-01 18:10:00', '2014-04-01 13:00:00' ) 

returns 05:10:00
hour('2014-04-01 13:00:00')

returns 13
increment the 13 by the hour value
seems long winded :(

A:

In C# to enumerate all (full) hours from start to end:
public static IEnumerable<DateTime> Hours(DateTime start, DateTime end)
{
    start -= TimeSpan.FromMinutes(start.TimeOfDay.TotalMinutes);
    end -= TimeSpan.FromMinutes(end.TimeOfDay.TotalMinutes);

    return Enumerable.Range(0, (int)Math.Ceiling((end - start).TotalHours))
        .Select(x => begin + TimeSpan.FromHours(x));
}

First we remove minutes from interval boundaries, we need whole hours only (so 10:30 and 11:10 will result in two hours, not just the difference between them). Then we create an enumeration from zero to number of whole hours, we'll use it to create offsets from beginning. I keep start DateTime using calculated TimeSpan as offset to keep track of date changes (for example if interval boundary is across two or more days).
If, for example, start is 2014/04/01 10:25 and end is 2014/04/01 13:20 then you'll get:

2014/04/01 10:00
2014/04/01 11:00
2014/04/01 12:00
2014/04/01 13:00

To get just time part change return type to IEnumerable<TimeSpan> and last Select() to:
.Select(x => (begin + TimeSpan.FromHours(x)).TimeOfDay);

In SQL it can be done but it's little bit more tricky because you need a working table with a sequence of integers. Please note following code is untested.
First you need to calculate that difference:
CEIL(TIMESTAMPDIFF(MINUTE, startDate, endDate) / 60)

Now you can use a working table filled with numbers for offsets:

Value
-----
0
1
2
3
...

With this:
SELECT
    DATE_ADD(startDate, INTERVAL Value HOUR)
FROM 
    NumericTable
WHERE
    Value <= CEIL(TIMESTAMPDIFF(MINUTE, startDate, endDate) / 60)

In both cases (C# and SQL) you may replace output DateTime/Date with a TimeSpan/TIME object).

