<p>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.</p>

<p>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:</p>

<p><a href="https://i.stack.imgur.com/HHrs0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HHrs0.png" alt="Example Chart"></a></p>

<p>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.</p>

<p>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.</p>

[{'answer_id': 80125, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>I came up with a pseudocode solution, hope it helps.</p>\n\n<pre><code>create an array named timetable with 24 entries\ninitialise timetable to zero\n\nfor each user in SQLtable\n  firsthour = user.firsthour\n  lasthour = user.lasthour\n\n  firstminutes = 4 - (rounded down integer(user.firstminutes/15))\n  lastminutes = rounded down integer(user.lastminutes/15)\n\n  timetable(firsthour) = timetable(firsthour) + firstminutes\n  timetable(lasthour) = timetable(lasthour) + lastminutes\n\n  for index=firsthour+1 to lasthour-1\n    timetable(index) = timetable(index) + 4\n  next index\n\nnext user\n</code></pre>\n\n<p>Now the timetable array holds the values you desire in 15 minute granularity, ie. a value of 4 = 1 hour, 5 = 1 hour 15 minutes, 14 = 3 hours 30 minutes.</p>\n'}, {'answer_id': 80134, 'author': 'Mike Farmer', 'author_id': 4082, 'author_profile': 'https://Stackoverflow.com/users/4082', 'pm_score': 3, 'selected': True, 'text': "<p>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:</p>\n\n<pre><code>TIME_DIM\n -id\n -time_of_day\n -interval_15 \n -interval_30\n</code></pre>\n\n<p>an example of the data in the table would be</p>\n\n<pre><code>id   time_of_day    interval_15    interval_30\n1    00:00          00:00          00:00\n...\n30   00:23          00:15          00:00\n...\n100  05:44          05:30          05:30\n</code></pre>\n\n<p>Then all you have to do is join your table to the time dimension and then group by interval_15.  For example:</p>\n\n<pre><code>SELECT b.interval_15, count(*) \nFROM my_data_table a\nINNER JOIN time_dim b ON a.time_field = b.time\nWHERE a.date_field = now()\nGROUP BY b.interval_15\n</code></pre>\n"}, {'answer_id': 80155, 'author': 'pjz', 'author_id': 8002, 'author_profile': 'https://Stackoverflow.com/users/8002', 'pm_score': 0, 'selected': False, 'text': '<p>Here\'s another pseudocode solution from a different angle; a bit more intensive because it does 96 queries for every 24hr period:</p>\n\n<pre><code>results = []\nfor time in range(0, 24, .25):\n  amount = mysql("select count(*) from User_Activity_Table where time &gt;= start_time and time &lt;= end_time")\n  results.append(amount)\n</code></pre>\n'}, {'answer_id': 81667, 'author': 'Mike Woodhouse', 'author_id': 1060, 'author_profile': 'https://Stackoverflow.com/users/1060', 'pm_score': 0, 'selected': False, 'text': '<p>How about this:</p>\n\n<p>Use that "times" table, but with two columns, containing the 15-minute intervals. The from_times are the 15-minutely times, the to_times are a second before the next from_times. For example 12:30:00 to 12:44:59.</p>\n\n<p>Now get your person work table, which I\'ve called "activity" here, with start_time and end_time columns.</p>\n\n<p>I added values for Alice and Bob as per the original question.</p>\n\n<p>Here\'s the query from MySQL:</p>\n\n<pre><code>SELECT HOUR(times.from_time) AS \'TIME\', count(*) / 4 AS \'HOURS\'\nFROM times\n  JOIN activity\n  ON times.from_time &gt;= activity.start_time AND \n     times.to_time   &lt;= activity.end_time\nGROUP BY HOUR(times.from_time)\nORDER BY HOUR(times.from_time)\n</code></pre>\n\n<p>which gives me this:</p>\n\n<pre><code>TIME   HOURS\n12     0.7500\n13     1.0000\n14     1.0000\n15     1.5000\n16     2.0000\n17     1.0000\n18     1.0000\n19     0.7500\n</code></pre>\n\n<p>Looks about right...</p>\n'}]
['https://Stackoverflow.com/questions/79789', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/9867/']