Calculate no. of working hours between two given dates(exclude the weekends)

Question

I want to calculate no. of working hours between two given dates(excluding weekends) and it should be within business hours(8:00 AM - 5:00 PM).

I have 3 tables:

table 1 contains open dates and closed dates for different jobs.
table 2 contains the working hours(2 columns start time(8:00 AM) and end time(5:00 PM)).
Table 3 contains date and the day on that date.

For eg.

open date - 2015-10-16 06:00:00 & closed date - 2015-10-19 15:00:00 working_hours_start - 8:00 AM & working_hours_end - 5:00 PM

Answer - 16 hrs

score 4 · Answer 1 · answered Jul 20 '16 at 07:33

I had this requirement and have written complete function that can calculate while avoiding hours of weekend and holidays for a given country (using a separate table). I have put the whole function and details on my blog (http://mgw.dumatics.com/mysql-function-to-calculate-elapsed-working-time/) along with explanation and flowchart and creation of holiday table etc...

Example of problem resolved:

Let's say an incident was logged on "Friday 10th June 2016 at 12:00" for a site in the "UK" which opens between 09:00 to 16:00. This incident was then closed on "Tuesday 14th June 2016 at 14:00".

For the above incident function should calculate the age as 960 minutes = 16 hours = [4 hours on Friday (12:00 to 16:00) + 7 hours on Monday (09:00 to 16:00) + 5 hours on Tuesday (09:00 to 14:00)]

score 1 · Answer 2 · edited Jan 25 '18 at 16:11

CREATE FUNCTION WorkTime 
(
    reported_datetime DATETIME,
    closed_datetime   DATETIME
)
RETURNS DOUBLE

BEGIN

    DECLARE FirstDay, LastDay, CurrentDate, LastDate DATE;
    DECLARE StartTime, FinishTime, WorkStart, WorkFinish, DailyWorkTime TIME;

    DECLARE Temp DOUBLE;
    SET     Temp = 0;

    SET FirstDay   = DATE(reported_datetime);
    SET LastDay    = DATE(closed_datetime);
    SET StartTime  = TIME(reported_datetime);
    SET FinishTime = TIME(closed_datetime);
    SET WorkStart  = '08:00:00';
    SET WorkFinish = '17:00:00';
    SET DailyWorkTime = TIMEDIFF(WorkFinish,WorkStart);

    IF (StartTime < WorkStart)
    THEN
        SET StartTime = WorkStart;
    END IF;
    IF (FinishTime > WorkFinish)
    THEN
        SET FinishTime = WorkFinish;
    END IF;

    SET CurrentDate    = FirstDay;
    SET LastDate       = LastDay;
    WHILE(CurrentDate <= LastDate)
    DO       
        IF (DAYOFWEEK(CurrentDate)!=1 AND DAYOFWEEK(CurrentDate)!=7)
        THEN
            IF (CurrentDate != FirstDay) AND (CurrentDate != LastDay)
            THEN
                SET Temp = Temp + TIME_TO_SEC(DailyWorkTime)/3600;

            ELSEIF (CurrentDate = FirstDay) AND (CurrentDate != LastDay) 
            THEN
                SET Temp = Temp + GREATEST(TIME_TO_SEC(TIMEDIFF(WorkFinish,StartTime)),0)/3600;

            ELSEIF (CurrentDate != FirstDay) AND (CurrentDate = LastDay)
            THEN
                SET Temp = Temp + GREATEST(TIME_TO_SEC(TIMEDIFF(FinishTime,WorkStart)),0)/3600;

            ELSEIF (CurrentDate = FirstDay) AND (CurrentDate = LastDay)
            THEN
                SET Temp = TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime))/3600;

            END IF;
        END IF;

        SET CurrentDate = DATE_ADD(CurrentDate, INTERVAL 1 DAY);
    END WHILE;

    IF Temp < 0
    THEN
        SET Temp = 0; 
    END IF;
    RETURN Temp;
END$$;

Abhishek Sharma · Answer 3 · 2015-10-19T10:18:51.580

0

Try mysql timediff

time_to_sec(timediff('2015-10-16 06:00:00', '2015-10-19 15:00:00 ' )) / 3600

Day not saturday and sunday

where WEEKDAY(date) !=5 and WEEKDAY(date) !=6

and try query like this demo

select *,time_to_sec(timediff(date_note1, date_note2)) / 3600 from (SELECT CASE
    WHEN DATE_FORMAT(date,'%H:%i:%s')  < '8:00:00' THEN CONCAT(date, ' 8:00:00')
    ELSE date
    END AS date_note1 ,

    CASE
    WHEN DATE_FORMAT(date2,'%H:%i:%s')  > '17:00:00' THEN CONCAT(date2, ' 17:00:00')
    ELSE date2
    END AS date_note2

    FROM `test` where WEEKDAY(date) !=5 and WEEKDAY(date) !=6) as tbl

edited Oct 19 '15 at 10:18

answered Oct 19 '15 at 08:00

Abhishek Sharma

6,689
1
14
20

Yes, but it will show the total time between these two dates which will include saturday and sunday and it will also include the time other than business hours, which i want to exclude. – Tyson_07 Oct 19 '15 at 09:21
The given query will convert the start_datetime to 8:00:00 if it is less then 8:00:00 else it will remain same, and same for end_datetime and will calculate the total hours suppose from 8:00 of 16oct to 15:00 of 18oct which will include extra hours. What I want is that for 16 oct it should calculate from 8:00 - 17:00, same for 17 oct and 8:00 to 15:00 for 18 oct. – Tyson_07 Oct 19 '15 at 11:25
Also here "where WEEKDAY(date) !=5 and WEEKDAY(date) !=6" which date should I include, it will accept either the start date or end date and will check for that particular date....but it should also check for the days for dates in between those given dates. – Tyson_07 Oct 19 '15 at 11:28
I am new to queries ....so please help. Here is the similar question but in sql, can you convert it to mysql. http://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates – Tyson_07 Oct 19 '15 at 11:38

Calculate no. of working hours between two given dates(exclude the weekends)

3 Answers3