SQL calculate processing time for business hours (including special hours on friday)

Question

I am looking for a SQL function, that returns the processing time of a ticket. The ticket comes along with two timestamps:

start_time = when the ticket was submited end_time = when ticket was processed

If it wasn't processed yet, the end_time is NULL and this ticket is not included in the query where I call the function:

SELECT AVG(MY_SEARCHED_FUNCTION(start_time , end_time)) AS difference
FROM processing_orders
WHERE end_time IS NOT NULL

It is important that the function only includes business ours which are: MO-TH 07:00-17:00 FR 07:00-13:00

So for example, when a ticket comes on friday 15:00 and is processed monday 08:00, the caluclated difference should be 1 hour.

I already know the answer from: MySQL - Average difference between timestamps, excluding weekends and out of business hours

DROP FUNCTION IF EXISTS BUSINESSHOURSDIFF;
DELIMITER $$
CREATE FUNCTION BUSINESSHOURSDIFF(start_time TIMESTAMP, end_time TIMESTAMP)
RETURNS INT UNSIGNED
BEGIN
IF HOUR(start_time) > 17 THEN SET start_time = CONCAT_WS(' ', DATE(start_time), '17:00:00');
END IF;
IF HOUR(start_time) < 8 THEN SET start_time = CONCAT_WS(' ', DATE(start_time), '08:00:00');
END IF;
IF HOUR(end_time) > 17 THEN SET end_time = CONCAT_WS(' ', DATE(end_time), '17:00:00');
END IF;
IF HOUR(end_time) < 8 THEN SET end_time = CONCAT_WS(' ', DATE(end_time), '08:00:00');
END IF;
RETURN 45 * (DATEDIFF(end_time, start_time) DIV 7) + 
          9 * MID('0123455501234445012333450122234501101234000123450', 
                  7 * WEEKDAY(start_time) + WEEKDAY(end_time) + 1, 1) + 
          TIMESTAMPDIFF(HOUR, DATE(end_time), end_time) - 
          TIMESTAMPDIFF(HOUR, DATE(start_time), start_time);
END $$
DELIMITER ;

This however doesnt include the friday specifications.

score 0 · Answer 1 · answered Mar 18 '22 at 11:06

I did not understand completely what you want to do and in order to play your code I changed it a little bit. I think this will help you:

CREATE procedure BUSINESSHOURSDIFF(start_time TIMESTAMP, end_time TIMESTAMP)
BEGIN
    IF DAYOFWEEK(end_time) = 6 THEN
        IF HOUR(start_time) > 17 THEN SET start_time = CONCAT_WS(' ', DATE(start_time), '17:00:00');
        END IF;
        IF HOUR(start_time) < 8 THEN SET start_time = CONCAT_WS(' ', DATE(start_time), '08:00:00');
        END IF;
        IF HOUR(end_time) > 17 THEN SET end_time = CONCAT_WS(' ', DATE(end_time), '17:00:00');
        END IF;
        IF HOUR(end_time) < 8 THEN SET end_time = CONCAT_WS(' ', DATE(end_time), '08:00:00');
        END IF;
        select 45 * (DATEDIFF(end_time, start_time) DIV 7) + 
                  9 * MID('0123455501234445012333450122234501101234000123450', 
                          7 * WEEKDAY(start_time) + WEEKDAY(end_time) + 1, 1) + 
                  TIMESTAMPDIFF(HOUR, DATE(end_time), end_time) - 
                  TIMESTAMPDIFF(HOUR, DATE(start_time), start_time) as date;
    ELSE
    select 'error';
    END IF;
END;


call BUSINESSHOURSDIFF(now(), DATE_SUB(NOW(), INTERVAL 7 DAY));

So, today, 2022.03.18 is Friday it returns a value, but if you change to "DATE_SUB(NOW(), INTERVAL 6 DAY)" it will throw error. I think you rearrange your code according to the this.

Note: Mysql WEEKDAY() vs Mysql DayofWeek()

From the Mario' s answer:

"On the other hand dayofweek counts the days of the week from Sunday and start with 1

1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday"

SQL calculate processing time for business hours (including special hours on friday)

1 Answers1