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?
I guess you're dealing with table rows meaning
person x was in the store from 2017-02-28 12:25 to 2017-02-28 14:10
and similar sorts of information. You have a decision to make about how to score your hours. Here are some alternatives, from which you can pick one.
If the time interval in each sample covers the top of the hour, then you give that hour a +1. For my example row the hours for 13:00 and 14:00 will get +1. This is probably easiest to implement. It will not count people who arrived at 13:10 and left at 13:20.
If the time interval in each sample intersects at all with the time interval from a half-hour before the hour to just under a half-hour after, it gets +1. This counts everybody.
It's easy and reasonably cheap (comments to the contrary notwithstanding) to convert UNIX timestamps to date / time stamps:
SELECT FROM_UNIXTIME(1488310427)
gets a time close to when I wrote this.
SELECT HOUR(FROM_UNIXTIME(1488310427));
gets the hour of the day in which that timestamp occurred. These operations honor the current time zone setting of your MySQL connection. For example, if you want the hour of the day in Atlantic Standard Time, do this.
SET time_zone='America/Halifax';
SELECT HOUR(FROM_UNIXTIME(1488310427));