PHP - Time completion in working hours
العربية
български
català
中文
čeština
dansk
Nederlands
eesti
suomi
français
Deutsch
Ελληνικά
עברית
हिंदी
magyar
Bahasa Indonesia
italiano
日本語
한국어
latviešu
lietuvių
norsk
polski
Português
română
русский
slovenčina
slovenski
español
svenska
ไทย
Türkçe
українська
Tiếng Việt
Okay, not so much a programming problem, but I can only seem to think of really complicated methods to solve this.
There is a script we run in the office. We need to determine how long this script takes only in working hours (working hours being 9am - 5pm). For example, let's say the script is started on Monday at 2pm, and finishes on Tuesday at 4pm. This script has taken 26 hours to complete, but only 10 hours to complete. This script can finish in a single working day, but can also span over many days. The script can only start and end in a working day. The timestamping is done with Unix format.
I do not have a clue how this could be done in SQL, but I know the network admin would prefer this.
The server-side scripting language is PHP. So this would be the second option available. My attempt is below:
$workingDay = 24;
$workingDayHours = 8;
$workingTime = 0;
while($timeTaken >= $workingDay) {
$workingTime += $workingDayHours;
$timeTaken -= $workingDay;
}
$workingTime += $timeTaken;
I can't really think properly right now, too much work on, so although this method is dumb, it seems to work in this scenario since the $timeTaken will always be <= 8 || >= 24.
Anyways, would be great to see a better and smarter method (perhaps one that would work with any number of hours). SQL would be great to see too.
Answer |
something like this (will only work if the job starts and completes between 9:00-17:00)... (not tested sorry)
The elapsed duration in hours:
HOUR(TIMEDIFF(enddatetime-startdatetime))
To calculate the number of working hours:
We need to remove the n 16 hour periods that the job was not running, (any job running for more than 16 hours must be running over n days)
duration - (FLOOR(duration/16)*16)
to get:
SELECT HOUR(TIMEDIFF(enddatetime-startdatetime)) -
(FLOOR(HOUR(TIMEDIFF(enddatetime-startdatetime))/16)*16) AS duration
If you want to remove weekends you could simply look at the start and end week number, if they are not the same remove 48 hours (for each weekend the job runs over), then remove this value from the above duration
(week(enddatetime,1) - week(startdatetime,1)) * 48