MySQL TimeDiff to exclude weekends

Question

I've been using TimeDiff on a MySQL table to get the difference between 2 fields, both in the DateTime format. Here's the query I'm using, which also limits the durations down to this year alone.

SELECT username, CONCAT(
FLOOR(SUM(HOUR(TIMEDIFF(end_time, start_time)) / 24)), ' days ',
MOD(HOUR(TIMEDIFF(end_time, start_time)), 24), ' hours ',
MINUTE(TIMEDIFF(end_time, start_time)), ' minutes')
AS duration
FROM table
WHERE start_time > CONCAT(YEAR(CURDATE()) -1, '-12-31')
GROUP BY username

The problem I'm having is that I've been having difficulty in trying to work out how to exclude weekends from the result. Can anyone help please?

There are some great ideas [here](http://stackoverflow.com/questions/2222563/mysql-calculate-the-difference-between-date-times-only-during-m-f-work-week). — Alain Collins, Nov 09 '12 at 18:47
Define "exclude weekends from the result". Do you mean to subtract the weekend hours from the `duration`, or exclude records that start, end, or contain a weekend? — Ross Smith II, Nov 09 '12 at 21:36
He actually wants to subtract the weekend hours from the duration. — Surinder, Feb 12 '17 at 16:11

score 4 · Answer 1 · answered Nov 09 '12 at 21:42

For the purpose of example we use static @start and @end dates, but in practice you can replace them with your column names and all of these values will be recalculated per-row.

SET @start  = '2012-09-30';
SET @end    = '2012-11-03';

SELECT
    @raw_days   := DATEDIFF(@end, @start)+1 'raw_days',
    @full_weeks := FLOOR(@raw_days / 7) 'full_weeks',
    @odd_days   := @raw_days - @full_weeks * 7 'odd_days',
    @wday_start := DAYOFWEEK(@start) 'wday_start',
    @wday_end   := DAYOFWEEK(@end) 'wday_end',
    @weekend_intrusion  := @wday_start + @odd_days 'weekend_intrusion',
    @extra_weekends     :=
        IF(@wday_start = 1, IF(@odd_days = 0, 0, 1),
            IF(@weekend_intrusion > 7, 2,
                IF(@weekend_intrusion > 6, 1, 0)
            )
        ) 'extra_weekends',
    @total_weekends     := @full_weeks * 2 + @extra_weekends 'total_weekends',
    @total_workdays     := @raw_days - @total_weekends 'total_workdays'

The IF statements boil down to:

If the week starts on a Sunday, and there are no 'odd' days, then there are no extra weekend days. If there are odd days, then there can only be 1 weekend day since it can't possibly stretch to Saturday since that would be a 'full' week.

Otherwise, we see if the remaining portion of a week extends past Sunday. If so, add 2 weekend days. Else if the portion goes to Saturday, add 1 weekend day. Else 0.

Output:

+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
| raw_days | full_weeks | odd_days | wday_start | wday_end | weekend_intrusion | extra_weekends | total_weekends | total_workdays |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
|       34 |          4 |        6 |          1 |        6 |                 7 |              1 |              9 |             25 |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+

marengaz · Answer 2 · 2013-11-06T10:32:48.280

heres a bulk solution that i just developed using my date dimension table it allows start and end to be during a weekend.

Select
    f.id, f.dtStart, f.dtEnd,
    sec_to_time(
    sum(
        case 
            -- don't count weekends
            when day_in_week in (6,7) 
                then 0
            -- start and end on same day
            when date(f.dtStart) = date(f.dtEnd) 
                then UNIX_TIMESTAMP(f.dtEnd) - UNIX_TIMESTAMP(f.dtStart)
            -- start period
            when date(f.dtStart) = dt.date_value
                then 24*60*60 - time_to_sec(time(f.dtStart))
            -- end period
            when date(f.dtEnd) = dt.date_value
                then time_to_sec(time(f.dtEnd))
            -- middle days
            else 24*60*60
        end
    )
    ) INT_timediff,
From fact f
join dim_date dt
    on dt.date_value between date(f.dtStart) and date(f.dtEnd)
group by f.id, f.dtStart, f.dtEnd

Ross Smith II · Answer 3 · 2014-04-28T21:35:06.607

This should work:

SELECT 
username, 
CONCAT(
    FLOOR(
        SUM(
            UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) - 86400 * (
                (FLOOR(DATEDIFF(end_time, start_time) / 7) * 2) +
                    IF(WEEKDAY(end_time) > 4, WEEKDAY(end_time) - 4, 0) +
                    IF(WEEKDAY(end_time) < WEEKDAY(start_time), 2, 0)
            )
        ) / 86400
    ),
    ' days ',
    TIME_FORMAT(
    SEC_TO_TIME(
        SUM(
            UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) - 86400 * (
                (FLOOR(DATEDIFF(end_time, start_time) / 7) * 2) +
                    IF(WEEKDAY(end_time) > 4, WEEKDAY(end_time) - 4, 0) + 
                    IF(WEEKDAY(end_time) < WEEKDAY(start_time), 2, 0)
            )
        ) % 86400
    ),
    '%H hours %i minutes'
    )
) AS duration
FROM table
WHERE start_time > CONCAT(YEAR(CURDATE()) -1, '-12-31')
GROUP BY username;

but it assumes that start_time and end_time never falls during a weekend.

See http://sqlfiddle.com/#!2/d41d8/3587 for a working example.

If you don't care about an extra column, this query can be simplified as:

SELECT
@diff := SUM(
    UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) - 86400 * (
        (FLOOR(DATEDIFF(end_time, start_time) / 7) * 2) +
            IF(WEEKDAY(end_time) > 4, WEEKDAY(end_time) - 4, 0) + 
            IF(WEEKDAY(end_time) < WEEKDAY(start_time), 2, 0)
    )
) AS duration_seconds,
CONCAT(
    FLOOR(@diff / 86400),
    ' days ',
    TIME_FORMAT(SEC_TO_TIME(@diff % 86400), '%H hours %i minutes')
) AS duration
FROM table
WHERE start_time > CONCAT(YEAR(CURDATE()) -1, '-12-31')
GROUP BY username;

MySQL TimeDiff to exclude weekends

3 Answers3

Linked