Link to home
Start Free TrialLog in
Avatar of TheBull369
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?
Avatar of Derek Jensen
Derek Jensen
Flag of United States of America image

Wow....umm, I was paid several months' salary to figure this problem out, and it didn't even involve time...

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. :)
Avatar of TheBull369
TheBull369

ASKER

So in a nutshell what your telling me is this is too complicated to be solved within a MySQL query? :-)
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial