Select/Query calculate time between timestamps without weekends

Question

Problem presented is to calculate for each row returned the time ("ResponseTime") between 2 timestamps ("StartDateTime" and "EndDateTime") excluding the weekends. Does not take into consideration Work hours or Holidays.

Weekends in this case are defined as Saturday 00:00:00 to Sunday 23:59:59.

Had a tough time coming up with a solution for this question so thought I would share my final product. Found lots of solutions online but most either used a calendar table, which I couldn't use in this application, or had a logic I didn't understand. Solution shared below. Please feel free to offer your own solution based on the problem or to correct any errors you see in my code. Regards,

EDIT: as per comments provided by @JuanCarlosOropeza solution I presented is not optimal. Providing sample data for him to forward a different solution. If anyone has improvements as well feel free to participate.

CREATE TABLE SourceTable
    (`id` int, `StartDateTime` datetime, `EndDateTime` datetime)
;

INSERT INTO SourceTable
    (`id`, `StartDateTime`, `EndDateTime`)
VALUES
    (1, '2016-09-20 12:52:00', '2016-09-23 13:15:00'),
    (2, '2016-09-19 19:15:00', '2016-09-22 19:15:00'),
    (3, '2016-09-01 10:35:00', '2016-09-06 13:15:00'),
    (4, '2016-09-26 10:34:00', '2016-09-29 11:25:00'),
    (5, '2016-09-01 13:01:00', '2016-09-06 14:55:00'),
    (6, '2016-09-05 02:21:00', '2016-09-08 19:15:00'),
    (7, '2016-09-27 14:14:00', '2016-10-01 19:15:00'),
    (8, '2016-09-27 04:18:00', '2016-09-30 14:15:00'),
    (9, '2016-09-01 14:50:00', '2016-09-06 17:25:00'),
    (10, '2016-09-20 12:52:00', '2016-09-23 13:15:00'),
    (11, '2016-09-26 02:14:00', '2016-09-29 10:15:00'),
    (12, '2016-09-01 12:04:00', '2016-09-06 17:05:00'),
    (13, '2016-09-20 15:30:00', '2016-09-23 15:15:00'),
    (14, '2016-09-02 16:04:00', '2016-09-07 20:55:00'),
    (15, '2016-09-23 10:41:00', '2016-09-28 13:05:00'),
    (16, '2016-09-27 16:28:00', '2016-10-01 13:15:00'),
    (17, '2016-09-27 15:33:00', '2016-10-01 22:45:00'),
    (18, '2016-09-20 12:53:00', '2016-09-23 13:25:00'),
    (19, '2016-09-19 13:49:00', '2016-09-22 13:05:00'),
    (20, '2016-09-20 13:46:00', '2016-09-23 13:15:00'),
    (21, '2016-09-01 16:32:00', '2016-09-06 18:05:00'),
    (22, '2016-09-01 10:35:00', '2016-09-06 22:45:00'),
    (23, '2016-09-26 12:40:00', '2016-09-29 12:35:00'),
    (24, '2016-09-27 10:37:00', '2016-09-30 21:25:00'),
    (25, '2016-09-27 09:41:00', '2016-09-30 15:15:00'),
    (26, '2016-09-16 02:09:00', '2016-09-21 10:05:00'),
    (27, '2016-09-20 15:13:00', '2016-09-23 15:15:00'),
    (28, '2016-09-20 15:30:00', '2016-09-23 15:15:00'),
    (29, '2016-09-27 09:55:00', '2016-09-30 13:25:00'),
    (30, '2016-09-27 04:18:00', '2016-09-30 14:15:00')
;

If you couldnt create the calendar table why you didnt use a subquery for the calendar? — Juan Carlos Oropeza, Dec 13 '16 at 21:05
Not sure I understand your question juanca. I had no access to the database so couldn't create a calendar table to include in the subquery you mention. Also this application did not need to consider holidays but only weekends. Are you suggesting using the calendar table to check the weekends? That would generally be better than the complex case structure I made I agree but sadly not applicable here. I think? Or are you stating including the full calendar table in my query as a temp table? — nbayly, Dec 13 '16 at 21:12
Yes, I mean create the calendar table using a query without a db table — Juan Carlos Oropeza, Dec 13 '16 at 21:28
something like this http://stackoverflow.com/a/14105058/3470178 — Juan Carlos Oropeza, Dec 13 '16 at 21:29
I think the reason I didn't go this route is because I then didn't understand how to go from having a series of dates to exclude and intersecting that with the **datetime** range (start to end) to calculate the total time. On the example you provided they just left join to distinct dates and sum up a different field. Not quite what I was trying to accomplish as I need to exclude any dates between both timestamps, not just when the timestamps fall on a weekend day. Am I not understanding how your solution works? — nbayly, Dec 13 '16 at 21:56
OK, update your answer with some data and expected output and I show you — Juan Carlos Oropeza, Dec 13 '16 at 22:33
@JuanCarlosOropeza Sorry for the delay. I have provided the sample data. Excited to see what you are referring to. — nbayly, Dec 23 '16 at 18:06

score 0 · Answer 1 · answered Dec 13 '16 at 20:58

I created this solution considering the following logic assumptions.

StartDateTime always occurs before EndDateTime (though had some that didn't and it calculated the time difference correctly)

Week StartDateTime occurred: WEEK(StartDateTime,1)

Week EndDateTime occurred: WEEK(EndDateTime,1)

Start of weekend of week StartDateTime: ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime))

Start of workweek after first weekend: ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),7-WEEKDAY(StartDateTime))

Full Query:

SELECT
    id,
    StartDateTime,
    EndDateTime,
    CASE
    WHEN ( WEEK(EndDateTime,1) = WEEK(StartDateTime,1) )
        THEN
            CASE
            WHEN ( StartDateTime >= ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)) )
                THEN SEC_TO_TIME(0)
                ELSE
                    CASE
                    WHEN ( EndDateTime >= ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)) )
                        THEN ( TIMEDIFF(ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)),StartDateTime) )
                        ELSE ( TIMEDIFF(EndDateTime,StartDateTime) )
                    END
            END
        ELSE
            CASE
            WHEN ( StartDateTime >= ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)) )
                THEN
                    CASE
                    WHEN ( EndDateTime >= ADDDATE(ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)),(WEEK(EndDateTime,1) - WEEK(StartDateTime,1)) * 7) )
                        THEN ( SEC_TO_TIME(120*3600*(WEEK(EndDateTime,1) - WEEK(StartDateTime,1))) )
                        ELSE ( SEC_TO_TIME(120*3600*(WEEK(EndDateTime,1) - WEEK(StartDateTime,1) - 1) + TIME_TO_SEC(TIMEDIFF(EndDateTime, ADDDATE(ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),7-WEEKDAY(StartDateTime)),7*(WEEK(EndDateTime,1) - WEEK(StartDateTime,1) - 1))))) )
                    END
                ELSE
                    CASE
                    WHEN ( EndDateTime >= ADDDATE(ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)),(WEEK(EndDateTime,1) - WEEK(StartDateTime,1)) * 7) )
                        THEN ( SEC_TO_TIME(120*(WEEK(EndDateTime,1) - WEEK(StartDateTime,1)) + TIME_TO_SEC(TIMEDIFF(ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)),StartDateTime))) )
                        ELSE ( SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(EndDateTime, ADDDATE(ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),7-WEEKDAY(StartDateTime)),7*(WEEK(EndDateTime,1) - WEEK(StartDateTime,1) - 1)))) + TIME_TO_SEC(TIMEDIFF(ADDDATE(TIMESTAMP(DATE(StartDateTime),'00:00:00'),5-WEEKDAY(StartDateTime)),StartDateTime))) )
                    END
            END
    END as ResponseTime
FROM
    SourceTable;

First CASE checks if both timestamps happened on the same week. Second layer checks if StartDateTime happened during the first weekend. Third layer checks if EndDateTime happened during a weekend. Based on these considerations outputs the correct calculation.

You should Include the `CREATE TABLE` for `SourceTable` with some sample and outputs. Also maybe a working demo in http://rextester.com/l/mysql_online_compiler — Juan Carlos Oropeza, Dec 13 '16 at 21:09
Well I changed the script to be generic for everyone. The only need is to have 2 datetime fields for StartDateTime and EndDateTime. Doesn't even require a unique `id` like I placed. If given time I will try to create a working demo. Regards — nbayly, Dec 13 '16 at 21:16

Select/Query calculate time between timestamps without weekends

1 Answers1