当前位置：首页 > news >正文

mysql 当前时间加3个工作日

news 2025/8/3 7:38:58

1. 问题描述：

在日常工作中可能会遇到计算工作日的情况

2. 解决过程

(1) 首先制作一个假日表 holiday_config

CREATE TABLE `holiday_config` (`id` int(10) NOT NULL AUTO_INCREMENT,`holiday` varchar(8) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

其中id 为主键自增，holiday 为假期格式为 '%Y-%m-%d'

工作日：即不在 holiday_config 表中的数据

a. 增加的三天是否为假日，可能出现的情况有一下四种

情况1 ：如果无假日，则直接在原数据的基础上加三天即可，使用date_add(now(),interval 3 DAY);

情况2：增加的三天存在假日且小于3 那么直接用 date_add(now(),interval 3+（小于3的数） DAY);

情况3：增加的三天存在假日且等于3 那么直接用 date_add(now(),interval 3+3 DAY);

情况4：增加的三天存在假日且大于3 那么直接用 date_add(now(),interval 3+（大于3的数） DAY);

b. 如何获取范围内的假日数

假日数小于3 的情况

(SELECT COUNT(1)
FROM holiday_config
WHERE holiday BETWEEN now() AND DATE_ADD(now(), INTERVAL 3 DAY)
)

大于3 的情况可能有多种，目前只考虑连续假日小于等于10 的情况（具体看下方脚本）

(2) 最终结果

SELECTDATE_ADD(now(),INTERVAL 3 +case when   (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 3 DAY))<3then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 3 DAY))when (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 4 DAY))<4then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 4 DAY))when (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now() AND DATE_ADD(now(), INTERVAL 5 DAY))<5then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 5 DAY))when (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 6 DAY))<6then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 6 DAY))when (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 7 DAY))<7then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 7 DAY))when (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 8 DAY))<8then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 8 DAY))when (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 9 DAY))<9then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 9 DAY))when (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 10 DAY))<10then (SELECT COUNT(1)FROM holiday_configWHERE holiday BETWEEN DATE_ADD(now(),INTERVAL 1 DAY) AND DATE_ADD(now(), INTERVAL 10 DAY))endDAY) AS target_date;