MYSQL count hours passed exluding weekends

Question

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

I'm guessing that `stardate` is a parameter, but could that possibly be named `startdate` instead? — Paul T., Jul 10 '17 at 14:30
Yes its a parameter, updated the code to show the entire function for clearity — Breezer, Jul 10 '17 at 14:33
hahahha oh you were thinking about the misspelling there =P yeah I'll correct it, thank you for mentioning — Breezer, Jul 10 '17 at 14:39
Misspelling ok, as long it was used the same way throughout the handling, so no big deal. :-) — Paul T., Jul 10 '17 at 14:41
I ran your function as-is, and I get: 54 ... `WorkingHours('2017-07-05 12:17:18','2017-07-07 18:30:42')` returned `54` for me, which is about right 2 days (48hrs) + about 6hrs to be 54. — Paul T., Jul 10 '17 at 15:46
Also, I changed the end date to `2017-07-09` (to include the weekend), and still get 54. How are you calling/using the function? — Paul T., Jul 10 '17 at 16:02
@PaulT. I'm using it inside a query like so `WorkingHours(post_modified,CURRENT_TIMESTAMP)` — Breezer, Jul 10 '17 at 17:15
Same here, I basically did a `SELECT WorkingHours('2017-07-05 12:17:18','2017-07-07 18:30:42'` to try your function. What is an example value of `post_modified`? Perhaps that value may need a conversion? — Paul T., Jul 10 '17 at 17:18
that column is a timestamp, I guess current_timestamp has to be the function here acting strange, im running this code on a little older mysql has to be that. — Breezer, Jul 10 '17 at 17:28
Ok, I just tried with `CURRENT_TIMESTAMP`, and the result was different. I get 49, expecting more than the earlier 54, so there might be a slight mishandling in the function. What mysql version do you have? — Paul T., Jul 10 '17 at 17:33
nope wasn't current time stamp, try these dates and you'll get -45 ` SET @p0 = '2017-07-09 13:55:41'; SET @p1 = '2017-07-10 17:31:56'; SELECT `WorkingHours` ( @p0 , @p1 ) AS `WorkingHours` ; ` — Breezer, Jul 10 '17 at 17:34
I ran this query to check some of the function calculations for those dates: `select WEEKDAY('2017-07-09 13:55:41') + 1 as 'StartDay', (TIMESTAMPDIFF(HOUR,'2017-07-09 13:55:41','2017-07-10 17:31:56'))/(24*7) as 'TotWeeks', ROUND((((TIMESTAMPDIFF(HOUR,'2017-07-09 13:55:41','2017-07-10 17:31:56'))/(24*7))-FLOOR((TIMESTAMPDIFF(HOUR,'2017-07-09 13:55:41','2017-07-10 17:31:56'))/(24*7))) * 7) as 'RestDays', ROUND((0.1607*7*24) - (0*2*24 + (((1 + 7) - 5) * 24))) as 'IF calc' ` ... Give that a run to see the output, I'm running out of space for this comment. The 'If calc': uses the 1st 3 col. values — Paul T., Jul 10 '17 at 18:14
@PaulT. Yeah the fault is withing the logic inside the if statment, the problem occurs because im substracting to much from the total, I just can't figure out how I'm gonna take weekends into account with a general statment — Breezer, Jul 10 '17 at 19:07
There's likely a way, I'm sure. I'll check into alternatives, but I won't know until later today, or more likely, tomorrow. (If I get anywhere with my attempt) — Paul T., Jul 10 '17 at 19:10
A question on the 'working hours'... are you looking for 8 hrs, per day, for business days only (for example, one shift), or are there 3 shifts for each business day? I'm not clear 100% clear about hours calculation that you need? ... Also, what about holidays, is this a factor to be considered as well? — Paul T., Jul 11 '17 at 05:45
nope no need to take holidays into account or shifts, it's simply 24hours for everyday just want to exclude weekends thats all — Breezer, Jul 11 '17 at 05:49

score 1 · Accepted Answer · answered Jul 11 '17 at 18:02

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

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

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

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

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

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.

Using your code I made a final function that counts hours beetween 2 dates excluding weekends, it's pretty precise https://pastebin.com/HPxuBsaj — Breezer, Jul 14 '17 at 05:17

MYSQL count hours passed exluding weekends

1 Answers1