PHP to calculate the number of office hours between two dates

Question

We run a service that's based on a 2 business day SLA, so if the request is submitted at 1pm on Monday it should be completed by 1pm of Wednesday.

So in the database I have two MySQL timestamps YYYY/MM/DD HH:MM:SS, and need some way of working out the time between the two dates in working time, based on office hours of 9am till 5pm.

Can anyone give me any ideas?

Answer 1

In general terms, given timestamps A and B, where A is the start time:

• determine the number of days between A and B (there are easy ways to do this), call this D
• if the HH:MM:SS for timestamp B is greater or equal to that for A, then add the time difference H, giving D (days) + H (time)
• if the HH:MM:SS for timestamp B is less than that for A, subtract 1 from D and add 8 hours minus the time difference H, giving D - 1 (days) + 8 (hours) - H

In your example, if A is 1pm Monday and B is 1pm Wednesday, then D is 2. Since the times are the same, H is 0 and so the total difference is 2 working days.

If B were 3pm Wednesday, then the total difference would be 2 days plus 2 hours.

If B were 9am Wednesday, then the total difference would be 1 day plus 4 hours.

The above method doesn't handle weekends or holidays, so you would have to make the appropriate modifications.

Answer 2

The simplest way would be to store a table into your database with one line for each day, containing the working time available on that day. This lets you handle holidays and week-ends with high granularity, and you can get the amount of time available by running a sum over the days between the two dates specified by the user.

Answer 3

Creating this via SQL only is a tough one. In MySQL the difference between a timestamp can be calculated that way...

SELECT TIMESTAMPDIFF(HOUR,T.ts2,T.ts1) 
FROM test AS T;

...but that ofc doesn't take any office hours into consideration.

Please keep in mind that SQL doesn't know your office hours, and those can rely on other factors as well (weekends, bank holidays or whatever). In order to get that done, you'd have to first set a calendar table and then start the calculations.

I had a similar problem a few years ago, in there I used a backend script in which I defined the times and holidays. I then calculated the future timestamp while inserting the data in the first place, that was much easier and way more flexible.