Elegant method for drawing hourly bar chart from time-interval data?

Question

I have a list of timesheet entries that show a start and stop time. This is sitting in a MySQL database. I need to create bar charts based on this data with the 24 hours of the day along the bottom and the amount of man-hours worked for each hour of the day.

For example, if Alice worked a job from 15:30 to 19:30 and Bob worked from 12:15 to 17:00, the chart would look like this:

I have a WTFey solution right now that involves a spreadsheet going out to column DY or something like that. The needed resolution is 15-minute intervals.

I'm assuming this is something best done in the database then exported for chart creation. Let me know if I'm missing any details. Thanks.

Answer 1

Create a table with just time in it from midnight to midnight containing each minute of the day. In the data warehouse world we would call this a time dimension. Here's an example:

TIME_DIM
 -id
 -time_of_day
 -interval_15 
 -interval_30

an example of the data in the table would be

id   time_of_day    interval_15    interval_30
1    00:00          00:00          00:00
...
30   00:23          00:15          00:00
...
100  05:44          05:30          05:30

Then all you have to do is join your table to the time dimension and then group by interval_15. For example:

SELECT b.interval_15, count(*) 
FROM my_data_table a
INNER JOIN time_dim b ON a.time_field = b.time
WHERE a.date_field = now()
GROUP BY b.interval_15