mysql 除去周末,MySQL TimeDiff排除周末

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?

解决方案

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 |

+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+