mysql：用sql查询日期的工作日是否在两个工作日之间

6ie5vjzr 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(416)

我有这个vb.net代码，想在mysql中写一个函数来做同样的事情。

Public Function IsWeekdayTimeInRange(DateToCheck As DateTime, StartDayOfWeek As DayOfWeek,
                                                              EndDayOfWeek As DayOfWeek) As Boolean

    Dim WeekdaysInRange As New List(Of DayOfWeek)
    WeekdaysInRange.Add(StartDayOfWeek)
    Dim i As Integer = StartDayOfWeek
    While i <> EndDayOfWeek
        i += 1
        i = If(i = 7, 0, i)
        WeekdaysInRange.Add(i)
    End While

    Return WeekdaysInRange.Contains(DateToCheck.DayOfWeek)
End Function

在mysql中，我目前正处于这个阶段，但由于我从未编写过sql函数，因此需要一些帮助。

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result BOOLEAN;
    DECLARE i INT;
    SET result = false;
    SET weekdayToCheck = WEEKDAY(d);
    SET i = s;
    WHILE(i <> e) DO

        /* more logic here */

    END WHILE;

    RETURN result;
END; |

DELIMITER ;

编辑：自从我知道mysql Weekday(Monday) = 0 我的函数也需要转换为 Weekday(Sunday) = 0 因为我的数据使用这种格式。

sql mysql weekday

来源：https://stackoverflow.com/questions/51378190/mysql-find-out-whether-weekday-of-date-is-between-two-weekdays-with-sql

3条答案

按热度按时间

wb1gzix01#

不知道你对“中间”的定义是什么，但这里我用了大于和小于

CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result INT;
    SET weekdayToCheck = WEEKDAY(d);
    SET result = 0;

    IF (s < e) THEN 
       IF  (weekdayToCheck > s AND weekdayToCheck < e) THEN SET result = 1;
       END IF;
    END IF;

    IF (s > e) THEN
       IF (weekdayToCheck > s OR weekdayToCheck < e) THEN SET result = 1;
       END IF;        
    END IF;

    RETURN result; 
END;

也许所有的if/else子句都可以稍微缩短一些。

赞(0）回复(0）举报 2021-06-20

watbbzwu2#

现在可以了。它可能会更简洁，但它帮助我想清楚。

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT, FirstDayOfWeekIsSunday BOOLEAN)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result BOOLEAN;
    DECLARE i INT;
    SET result = false;
    SET weekdayToCheck = WEEKDAY(d);
    SET i = s;

    IF FirstDayOfWeekIsSunday = TRUE THEN
        SET weekdayToCheck = weekdayToCheck + 1;
        IF weekdayToCheck = 7 THEN
            SET weekdayToCheck = 0;
        END IF;
    END IF;

    IF weekdayToCheck = s THEN
        SET result = true;
    END IF;

    WHILE(i <> e) DO

        SET i = i + 1;

        IF i = 7 THEN
            SET i = 0;
        END IF;

        IF weekdayToCheck = i THEN 
            SET result = true;
        END IF;

    END WHILE;

    RETURN result;
END; |

DELIMITER ;

编辑：修复了循环中顺序的问题。还添加了用于处理函数的选项 s 以及 e 他们应该假设一周从星期天开始。编辑：测试了以上内容，效果良好。此外，还可以创建一个函数来检查datetime是否介于星期二下午3点和星期四凌晨2点之间。

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS_AND_TIME(d DATETIME, s INT, starttime TIME, e INT, endtime TIME, FirstDayOfWeekIsSunday BOOLEAN)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
    DECLARE weekdayToCheck INT;
    DECLARE result BOOLEAN;
    DECLARE i INT;
    DECLARE dTime TIME;
    SET result = false;
    SET weekdayToCheck = WEEKDAY(d);
    SET i = s;
    SET dTime = DATE_FORMAT(d, '%H:%i:%s');

    IF FirstDayOfWeekIsSunday = TRUE THEN
        SET weekdayToCheck = weekdayToCheck + 1;
        IF weekdayToCheck = 7 THEN
            SET weekdayToCheck = 0;
        END IF;
    END IF;

    IF weekdayToCheck = s THEN 
        IF ((weekdayToCheck = s AND dTime > starttime) OR (weekdayToCheck = e AND dTime < endtime) OR (weekdayToCheck <> s AND weekdayToCheck <> e)) THEN
            SET result = true;
        END IF;
    END IF;

    WHILE(i <> e) DO

        SET i = i + 1;

        IF i = 7 THEN
            SET i = 0;
        END IF;

        IF weekdayToCheck = i THEN 
            IF ((weekdayToCheck = s AND dTime > starttime) OR (weekdayToCheck = e AND dTime < endtime) OR (weekdayToCheck <> s AND weekdayToCheck <> e)) THEN
                SET result = true;
            END IF;
        END IF;

    END WHILE;

    RETURN result;
END; |

DELIMITER ;

赞(0）回复(0）举报 2021-06-20

yxyvkwin3#

考虑从周一开始的一周。希望这有助于：

DELIMITER |
CREATE FUNCTION IS_BETWEEN_WEEKDAYS(d DATETIME, s INT, e INT)
RETURNS BOOLEAN DETERMINISTIC
BEGIN
  DECLARE weekdayToCheck INT;
  DECLARE result TINYINT(1);
  SET weekdayToCheck =  WEEKDAY(d);
  SET result = 0;
  IF(weekdayToCheck > s AND weekdayToCheck < e) THEN
    SET result = 1;
  ELSEIF (s > e AND weekdayToCheck < s AND weekdayToCheck < e) THEN
    SET result = 1;
  END IF;

RETURN result;
END; |

DELIMITER ;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql：用sql查询日期的工作日是否在两个工作日之间

3条答案

相关问题

热门标签

最新问答