CODEWITHSUNDEEP
mysqldatetimemysql-function
Breezer
Breezer

Reputation: 10490

MYSQL count hours passed exluding weekends

I've come to a halt, I made a funtion inside mysql counting hours but it keeps giving me faulty numbers, I've been looking at it for hours and I just can't see what I am doing wrong

FUNCTION `WorkingHours`(`stardate` TIMESTAMP, `enddate` TIMESTAMP) RETURNS int(11)
BEGIN
DECLARE result DECIMAL(20,10) DEFAULT 0;
DECLARE TotWeeks DECIMAL(20,10);
DECLARE FullWeeks INT;
DECLARE RestDays DECIMAL(20,10);
DECLARE StartDay INT DEFAULT WEEKDAY(stardate) + 1;
SET TotWeeks = (TIMESTAMPDIFF(HOUR,stardate,enddate))/(24*7);
SET FullWeeks = FLOOR(TotWeeks);
SET RestDays = ROUND((TotWeeks-FullWeeks) * 7);
IF(RestDays + StartDay) > 5 THEN SET result = ROUND((TotWeeks*7*24) - (FullWeeks*2*24 + (((RestDays + StartDay) - 5) * 24)));
ELSE SET result = ROUND((TotWeeks*7*24) - (FullWeeks*2*24));
END IF;
RETURN result;
END

if anyone got any suggestions or an alternative approach I am more than willingly happy to replace this one.

Startdate:2017-07-05 12:17:18
Enddate:2017-07-07 18:30:42

Gives me -5

Edit: these dates gives -45

Startdate:2017-07-09 13:55:41
Enddate:2017-07-10 17:31:56

the function works almost everytime expect for the few times it doesn't and I jsut cant figure out why

Upvotes: 0

Views: 137

Answers (1)

Paul T.
Paul T.

Reputation: 4907

Ok, I crossed a function that does what you need. It simply counts weekdays for a date range.

DELIMITER $$

CREATE FUNCTION `CountWeekDays` (sdate VARCHAR(50), edate VARCHAR(50)) RETURNS INT

BEGIN
    #  first some variables for our procedure/function...
    DECLARE wdays, tdiff, counter, thisday smallint;
    DECLARE newdate DATE;
    #  now loop from start to end counting the loops and the number of weekdays...

    SET newdate := sdate;
    SET wdays = 0;

    #  return 1 if they're the same for "same day service"...
    if DATEDIFF(edate, sdate) = 0 THEN RETURN 1; END IF;
    #  if they're negative, return zero...
    if DATEDIFF(edate, sdate) < 0 THEN RETURN 0; END IF;

    label1: LOOP
        SET thisday = DAYOFWEEK(newdate);
        IF thisday BETWEEN 2 AND 6 THEN SET wdays := wdays + 1; END IF;
        SET newdate = DATE_ADD(newdate, INTERVAL 1 DAY);
        IF DATEDIFF(edate, newdate) < 0 THEN LEAVE label1; END IF;
    END LOOP label1;

    RETURN wdays;
END $$

DELIMITER ;

I found that function here.

Then to use it, here are a few example runs. Note that the time-portion really doesn't matter, as the function uses the date-portion.

select CountWeekDays('2017-07-05', CURRENT_TIMESTAMP) * 24 as WorkingHours

enter image description here

select CountWeekDays('2017-07-09 13:55:41', '2017-07-10 17:31:56') * 24 as WorkingHours

enter image description here

select CountWeekDays('2017-07-05', '2017-07-07') * 24 as WorkingHours

enter image description here

select CountWeekDays('2017-07-08', '2017-07-09') * 24 as WorkingHours

enter image description here

select CountWeekDays('2017-07-07', '2017-07-10') * 24 as WorkingHours

enter image description here

So try it out and see what you think. You can remove the * 24 portion of the query to see the weekday count for any given date range.

Upvotes: 1

Related Questions