I have a table with time_from
and time_to
in unix timestamp for a particular object. The time is an occupancy or usage figure, thus the time can span from say 1pm to 3pm.
What I would like to do is make a chart (have that ready in JS) similar to how Google shows store hours "favorite time" in Google Maps, ie. you can see which times there are more/less visitors. This should reflect all entries for the certain object name since the database was started.
I am unsure where to start, because I don't think averaging is the correct way to do this. I expect that if the occupied hours for one entry are 13:30 to 15:30, hours 13, 14, 15 each get +1. If the next entry is 12:00 to 13:40, 12 and 13 get +1.
In this short example, the chart would show 12, 14, 15 with 1 unit, 13 with 2 units.
One more problem arises though. How do I decide when to count an hour as +1? If the time is 15:05 to 16:05 it would make sense not to give 16 a +1. This would require some kind of calculation of minutes in the hour to decide whether or not that hour was "occupied".
How can this be queried from the database to get the correct units?