TheBull369
asked on
Time Difference Including Business Hours
I have a Mysql Table that has 2 timestamps fields in it: Date Created and Date Completed.
What I would like to do is calculate the length of time the document was open but I need the calculation to exclude weekends and non-business hours (business hours are 8:30am to 5:00pm). What is the best way to accomplish this?
What I would like to do is calculate the length of time the document was open but I need the calculation to exclude weekends and non-business hours (business hours are 8:30am to 5:00pm). What is the best way to accomplish this?
ASKER
So in a nutshell what your telling me is this is too complicated to be solved within a MySQL query? :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
However, there are plethora resources out there for determining/excluding weekends/holidays, and once you have just the business days, then it's a simple matter of multiplying that number by the number of business hours(8.5).
From there, depending on what you had for lunch, you could either add the remaining hours between the timestamps, or subtract the overflow hours from the extra first & last days. :)