MySQL TimeDiff 排除周末

Question

我一直在 MySQL 表上使用 TimeDiff 来获取 2 个字段之间的差异，这两个字段均采用 DateTime 格式。这是我正在使用的查询，它也将持续时间限制为仅今年。

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

我遇到的问题是，我一直难以弄清楚如何从结果中排除周末。有人可以帮忙吗？

Answer 1

出于示例的目的，我们使用静态 @start 和 @end 日期，但实际上您可以将它们替换为您的列名，所有这些值都将是每行重新计算。

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'

IF 语句归结为:

如果一周从星期日开始，并且没有“奇数”日，那么就没有额外的周末。如果有 奇数天，则只能有 1 个周末，因为它不可能延伸到星期六，因为那将是一个“完整”的一周。

否则，我们将查看一周的剩余部分是否会延续到周日之后。如果是这样，请添加 2 个周末。否则，如果该部分到星期六，则添加 1 个周末。否则为 0。

输出:

+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
| 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 |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+