An elegant method for drawing an hourly histogram with time interval data?

Question

I have a list of schedule entries that show the start and end times. It sits in a MySQL database. I need to create histograms based on this data with 24 hours on the bottom and the number of man-hours worked for each hour of the day.

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

I have a WTFey solution right now that includes a table looking at a DY column or something like that. The required resolution is 15 minute intervals.

I guess this is best done in a database and then exported to create a chart. Let me know if I lack details. Thank.

Answer 1

, . . :

TIME_DIM
 -id
 -time_of_day
 -interval_15 
 -interval_30

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

, , - , _15. :

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

Answer 2

, , .

create an array named timetable with 24 entries
initialise timetable to zero

for each user in SQLtable
  firsthour = user.firsthour
  lasthour = user.lasthour

  firstminutes = 4 - (rounded down integer(user.firstminutes/15))
  lastminutes = rounded down integer(user.lastminutes/15)

  timetable(firsthour) = timetable(firsthour) + firstminutes
  timetable(lasthour) = timetable(lasthour) + lastminutes

  for index=firsthour+1 to lasthour-1
    timetable(index) = timetable(index) + 4
  next index

next user

15- , .. 4 = 1 , 5 = 1 15 , 14 = 3 30 .

Answer 3

; , 96 24- :

results = []
for time in range(0, 24, .25):
  amount = mysql("select count(*) from User_Activity_Table where time >= start_time and time <= end_time")
  results.append(amount)

Answer 4

:

Use this "times" table, but with two columns containing 15 minute intervals. From time to time it is 15 minutes, to_times is a second until the next from_times. For example, from 12:30 to 12:44:59.

Now find your worksheet for the person I called "activity" here, with the start_time and end_time columns.

I added values ​​for Alice and Bob in accordance with the original question.

Here is a query from MySQL:

SELECT HOUR(times.from_time) AS 'TIME', count(*) / 4 AS 'HOURS'
FROM times
  JOIN activity
  ON times.from_time >= activity.start_time AND 
     times.to_time   <= activity.end_time
GROUP BY HOUR(times.from_time)
ORDER BY HOUR(times.from_time)

which gives me this:

TIME   HOURS
12     0.7500
13     1.0000
14     1.0000
15     1.5000
16     2.0000
17     1.0000
18     1.0000
19     0.7500

It looks right ...