CODEWITHSUNDEEP
mysqldatedatetime
Tyson_07
Tyson_07

Reputation: 21

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

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:

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

Upvotes: 1

Views: 4593

Answers (3)

Tyson_07
Tyson_07

Reputation: 21

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$$;

Upvotes: 1

techbolt
techbolt

Reputation: 103

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)]

Upvotes: 4

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

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

Upvotes: -1

Related Questions