MySQL TimeDiff排除周末 [英] MySQL TimeDiff to exclude weekends

问题描述

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

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?

推荐答案

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

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'

IF语句可归结为:

如果一周从星期日开始，并且没有奇数"天，则没有多余的周末.如果有 奇数天，那么只能有一个周末，因为它不可能延长到星期六，因为那将是一个完整"的星期.

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.

否则，我们将查看一周的剩余时间是否超过了星期日.如果是这样，请增加2个周末.否则，如果该部分去星期六，则增加1个周末.否则为0.

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.

输出:

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

这篇关于MySQL TimeDiff排除周末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！