MySQL排除节假日，计算日期差

符华-

已于 2023-09-13 18:00:41 修改

阅读量6.8k

点赞数 14

文章标签： mysql

于 2022-01-12 15:06:03 首次发布

本文链接：https://blog.csdn.net/weixin_43165220/article/details/122453242

版权

本文介绍如何计算两个日期之间的差值并排除节假日。通过建立节假日表和使用SQL查询，实现了精确计算两个日期间的有效工作日数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2023-09-13更新

需求

计算两个日期的差，并且需要排除节假日。

首先，我们需要准备一张节假日表，存放一年里所有的周末和法定节假日。

实现下面两个需求：

1、通过主表的两个日期字段，去计算这两个字段的日期差值；

2、传入两个日期，去计算这两个日期的差值；

通过主表的两个日期字段，去计算这两个字段的日期差值

主表

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `start_time` datetime NULL DEFAULT NULL,
  `end_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

INSERT INTO `test` VALUES (1, '2021-12-01 12:50:27', '2021-12-04 12:53:38');
INSERT INTO `test` VALUES (2, '2021-12-03 08:22:11', '2021-12-06 17:54:19');
INSERT INTO `test` VALUES (3, '2022-01-01 09:54:28', '2022-01-02 19:50:31');
INSERT INTO `test` VALUES (4, '2021-12-10 13:53:34', '2021-12-25 23:50:55');
INSERT INTO `test` VALUES (5, '2022-01-10 07:20:06', '2022-01-11 22:55:13');
INSERT INTO `test` VALUES (6, '2021-12-26 16:10:17', '2021-12-28 16:55:25');
INSERT INTO `test` VALUES (7, '2021-12-27 20:55:35', '2022-01-02 17:35:43');
INSERT INTO `test` VALUES (8, '2022-01-12 21:35:49', '2022-01-16 14:22:54');
INSERT INTO `test` VALUES (9, '2022-11-11 10:11:23', '2022-11-13 14:11:23');
INSERT INTO `test` VALUES (10, '2023-01-01 00:00:00', '2023-01-05 14:22:54');
INSERT INTO `test` VALUES (11, '2023-01-01 00:00:10', '2023-01-01 14:22:54');
INSERT INTO `test` VALUES (12, '2022-12-29 00:00:00', '2023-01-01 00:00:00');
INSERT INTO `test` VALUES (13, '2023-01-01 00:00:00', '2023-01-03 00:10:00');

节假日表（有21年和22年的节假日）

DROP TABLE IF EXISTS `sys_holiday`;
CREATE TABLE `sys_holiday`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `date` date NULL DEFAULT NULL COMMENT '日期',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '节日名称',
  `type` tinyint(1) NULL DEFAULT NULL COMMENT '节假日类型(0 工作日、1 周末、2 节日、3 调休)',
  `week` tinyint(1) NULL DEFAULT NULL COMMENT '1 - 7，分别表示 周一 至 周日',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建者',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '更新者',
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 348 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '节假日表' ROW_FORMAT = DYNAMIC;

INSERT INTO `sys_holiday` VALUES (1, '2022-06-04', '端午节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (2, '2022-08-06', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (3, '2022-02-20', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (4, '2021-10-03', '国庆节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (5, '2021-09-20', '中秋节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (6, '2022-12-10', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (7, '2021-06-13', '端午节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (8, '2022-06-25', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (9, '2022-01-08', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (10, '2021-10-01', '国庆节', 2, 5, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (11, '2022-06-18', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (12, '2022-12-04', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (13, '2022-05-28', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (14, '2021-05-16', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (15, '2021-10-02', '国庆节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (16, '2022-04-04', '清明节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (17, '2022-05-08', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (18, '2022-09-24', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (19, '2022-01-03', '元旦', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (20, '2021-03-13', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (21, '2021-04-04', '清明节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (22, '2021-01-10', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (23, '2021-04-03', '清明节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (24, '2022-10-03', '国庆节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (25, '2021-01-24', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (26, '2021-02-16', '初五', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (27, '2022-03-13', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (28, '2021-05-30', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (29, '2022-11-26', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (30, '2021-01-02', '元旦', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (31, '2022-05-03', '劳动节', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (32, '2022-05-15', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (33, '2021-02-28', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (34, '2021-08-15', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (35, '2021-04-11', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (36, '2021-07-11', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (37, '2021-01-03', '元旦', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (38, '2021-08-29', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (39, '2022-05-21', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (40, '2022-11-20', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (41, '2021-03-27', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (42, '2022-01-31', '除夕', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (43, '2022-07-09', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (44, '2022-02-26', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (45, '2022-11-12', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (46, '2022-06-11', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (47, '2022-12-18', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (48, '2022-09-17', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (49, '2022-08-07', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (50, '2021-08-21', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (51, '2021-06-12', '端午节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (52, '2021-01-23', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (53, '2022-04-09', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (54, '2021-11-14', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (55, '2021-09-19', '中秋节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (56, '2021-09-05', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (57, '2022-07-16', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (58, '2022-03-05', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (59, '2022-06-12', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (60, '2022-02-19', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (61, '2021-02-14', '初三', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (62, '2021-01-09', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (63, '2022-10-06', '国庆节', 2, 4, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (64, '2022-07-10', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (65, '2021-12-12', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (66, '2022-03-06', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (67, '2022-04-16', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (68, '2021-10-24', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (69, '2021-10-16', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (70, '2022-06-03', '端午节', 2, 5, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (71, '2022-10-02', '国庆节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (72, '2021-10-06', '国庆节', 2, 3, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (73, '2021-09-11', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (74, '2022-02-06', '初六', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (75, '2021-02-13', '初二', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (76, '2022-09-03', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (77, '2021-03-21', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (78, '2022-07-24', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (79, '2021-07-03', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (80, '2021-07-31', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (81, '2021-06-20', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (82, '2021-02-27', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (83, '2021-08-22', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (84, '2021-03-06', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (85, '2022-02-03', '初三', 2, 4, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (86, '2022-04-30', '劳动节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (87, '2021-06-19', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (88, '2021-07-25', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (89, '2022-12-25', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (90, '2021-12-18', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (91, '2022-11-27', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (92, '2022-04-17', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (93, '2021-05-01', '劳动节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (94, '2022-04-23', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (95, '2022-10-15', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (96, '2022-01-02', '元旦', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (97, '2021-12-05', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (98, '2021-07-04', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (99, '2022-09-25', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (100, '2022-03-12', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (101, '2021-09-21', '中秋节', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (102, '2021-09-25', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (103, '2022-06-19', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (104, '2022-12-24', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (105, '2022-01-09', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (106, '2022-10-16', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (107, '2021-02-12', '初一', 2, 5, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (108, '2022-07-31', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (109, '2022-08-21', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (110, '2021-05-02', '劳动节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (111, '2022-07-17', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (112, '2021-08-08', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (113, '2022-01-01', '元旦', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (114, '2021-11-28', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (115, '2021-07-24', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (116, '2022-01-22', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (117, '2021-11-13', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (118, '2021-06-14', '端午节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (119, '2021-10-04', '国庆节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (120, '2022-12-17', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (121, '2021-03-20', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (122, '2022-12-31', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (123, '2021-11-27', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (124, '2021-07-17', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (125, '2022-02-27', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (126, '2021-04-17', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (127, '2021-11-06', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (128, '2021-10-05', '国庆节', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (129, '2021-10-30', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (130, '2022-11-19', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (131, '2021-03-14', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (132, '2021-04-10', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (133, '2022-09-18', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (134, '2021-02-17', '初六', 2, 3, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (135, '2021-06-05', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (136, '2021-05-04', '劳动节', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (137, '2022-05-14', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (138, '2022-05-04', '劳动节', 2, 3, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (139, '2022-02-13', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (140, '2022-12-03', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (141, '2021-06-27', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (142, '2021-10-23', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (143, '2022-11-13', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (144, '2021-05-29', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (145, '2022-08-13', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (146, '2022-11-06', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (147, '2022-05-02', '劳动节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (148, '2021-04-18', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (149, '2021-05-23', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (150, '2021-01-01', '元旦', 2, 5, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (151, '2022-12-11', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (152, '2021-02-21', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (153, '2021-05-09', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (154, '2022-03-20', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (155, '2022-03-26', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (156, '2022-05-22', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (157, '2021-06-06', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (158, '2022-10-23', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (159, '2022-10-29', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (160, '2021-02-06', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (161, '2022-07-02', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (162, '2022-10-04', '国庆节', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (163, '2021-08-14', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (164, '2022-04-03', '清明节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (165, '2022-03-19', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (166, '2021-11-07', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (167, '2022-04-10', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (168, '2022-10-22', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (169, '2021-08-01', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (170, '2021-11-20', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (171, '2021-03-28', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (172, '2022-09-11', '中秋节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (173, '2021-01-16', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (174, '2022-07-23', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (175, '2021-12-04', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (176, '2022-07-30', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (177, '2021-07-10', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (178, '2022-02-12', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (179, '2021-08-28', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (180, '2021-08-07', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (181, '2021-12-11', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (182, '2021-05-03', '劳动节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (183, '2021-10-10', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (184, '2021-01-31', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (185, '2022-02-05', '初五', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (186, '2021-10-17', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (187, '2022-06-26', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (188, '2022-09-04', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (189, '2022-01-16', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (190, '2022-03-27', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (191, '2022-02-04', '初四', 2, 5, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (192, '2021-05-15', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (193, '2022-07-03', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (194, '2021-05-22', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (195, '2021-07-18', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (196, '2021-04-24', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (197, '2021-12-19', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (198, '2021-03-07', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (199, '2021-06-26', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (200, '2021-02-11', '除夕', 2, 4, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (201, '2021-12-26', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (202, '2022-10-05', '国庆节', 2, 3, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (203, '2022-09-12', '中秋节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (204, '2022-10-01', '国庆节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (205, '2022-10-07', '国庆节', 2, 5, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (206, '2022-06-05', '端午节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (207, '2022-04-05', '清明节', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (208, '2022-08-20', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (209, '2022-02-02', '初二', 2, 3, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (210, '2022-09-10', '中秋节', 2, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (211, '2021-10-07', '国庆节', 2, 4, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (212, '2021-09-12', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (213, '2021-11-21', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (214, '2022-01-23', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (215, '2021-04-05', '清明节', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (216, '2021-01-30', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (217, '2022-11-05', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (218, '2022-08-27', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (219, '2021-09-04', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (220, '2021-10-31', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (221, '2021-12-25', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (222, '2022-05-29', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (223, '2022-02-01', '初一', 2, 2, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (224, '2022-08-14', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (225, '2022-01-15', '周六', 1, 6, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (226, '2021-05-05', '劳动节', 2, 3, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (227, '2022-05-01', '劳动节', 2, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (228, '2022-10-30', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (229, '2021-01-17', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (230, '2022-08-28', '周日', 1, 7, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (231, '2021-02-15', '初四', 2, 1, NULL, '2023-02-10 14:38:14', NULL, '2023-02-10 14:38:14');
INSERT INTO `sys_holiday` VALUES (232, '2023-11-11', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (233, '2023-05-07', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (234, '2023-02-19', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (235, '2023-02-18', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (236, '2023-05-13', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (237, '2023-05-14', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (238, '2023-08-05', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (239, '2023-08-06', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (240, '2023-07-30', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (241, '2023-11-04', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (242, '2023-11-05', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (243, '2023-02-26', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (244, '2023-02-25', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (245, '2023-08-19', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (246, '2023-05-27', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (247, '2023-05-20', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (248, '2023-05-21', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (249, '2023-08-12', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (250, '2023-08-13', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (251, '2023-11-12', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (252, '2023-11-19', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (253, '2023-11-18', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (254, '2023-03-05', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (255, '2023-03-04', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (256, '2023-05-28', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (257, '2023-06-04', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (258, '2023-06-03', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (259, '2023-08-27', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (260, '2023-08-26', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (261, '2023-03-12', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (262, '2023-03-11', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (263, '2023-11-26', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (264, '2023-08-20', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (265, '2023-11-25', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (266, '2023-12-10', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (267, '2023-03-18', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (268, '2023-03-19', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (269, '2023-09-09', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (270, '2023-06-17', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (271, '2023-03-25', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (272, '2023-06-11', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (273, '2023-01-01', '元旦', 2, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (274, '2023-06-10', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (275, '2023-09-02', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (276, '2023-09-03', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (277, '2023-12-03', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (278, '2023-12-02', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (279, '2023-12-09', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (280, '2023-12-23', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (281, '2023-01-02', '元旦', 2, 1, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (282, '2023-03-26', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (283, '2023-01-08', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (284, '2023-06-18', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (285, '2023-01-07', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (286, '2023-04-01', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (287, '2023-04-02', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (288, '2023-09-17', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (289, '2023-06-22', '端午节', 2, 4, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (290, '2023-09-16', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (291, '2023-06-24', '端午节', 2, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (292, '2023-06-23', '端午节', 2, 5, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (293, '2023-12-16', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (294, '2023-09-10', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (295, '2023-12-17', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (296, '2023-12-30', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (297, '2023-12-31', '元旦', 2, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (298, '2023-01-15', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (299, '2023-04-09', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (300, '2023-01-14', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (301, '2023-04-05', '清明节', 2, 3, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (302, '2023-04-08', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (303, '2023-04-15', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (304, '2023-09-29', '中秋节', 2, 5, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (305, '2023-01-22', '初一', 2, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (306, '2023-07-01', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (307, '2023-01-23', '初二', 2, 1, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (308, '2023-09-24', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (309, '2023-01-21', '除夕', 2, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (310, '2023-07-02', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (311, '2023-09-23', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (312, '2023-10-01', '国庆节', 2, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (313, '2023-12-24', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (314, '2023-10-02', '国庆节', 2, 1, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (315, '2023-10-03', '国庆节', 2, 2, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (316, '2023-10-04', '国庆节', 2, 3, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (317, '2023-10-05', '国庆节', 2, 4, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (318, '2023-10-06', '国庆节', 2, 5, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (319, '2023-10-21', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (320, '2023-01-26', '初五', 2, 4, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (321, '2023-01-27', '初六', 2, 5, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (322, '2023-01-24', '初三', 2, 2, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (323, '2023-01-25', '初四', 2, 3, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (324, '2023-04-16', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (325, '2023-07-09', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (326, '2023-07-08', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (327, '2023-07-16', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (328, '2023-07-15', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (329, '2023-04-22', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (330, '2023-10-14', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (331, '2023-10-15', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (332, '2023-09-30', '中秋节', 2, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (333, '2023-02-05', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (334, '2023-02-04', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (335, '2023-04-29', '劳动节', 2, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (336, '2023-05-02', '劳动节', 2, 2, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (337, '2023-05-03', '劳动节', 2, 3, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (338, '2023-07-29', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (339, '2023-04-30', '劳动节', 2, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (340, '2023-07-23', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (341, '2023-02-12', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (342, '2023-07-22', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (343, '2023-02-11', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (344, '2023-05-01', '劳动节', 2, 1, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (345, '2023-10-22', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (346, '2023-10-28', '周六', 1, 6, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');
INSERT INTO `sys_holiday` VALUES (347, '2023-10-29', '周日', 1, 7, NULL, '2023-02-10 15:13:56', NULL, '2023-02-10 15:13:56');

排除节假日，计算日期差

SELECT id,start_time,end_time,secondcount,
CONCAT(FLOOR(secondcount/(3600*24)), '天', FLOOR(secondcount%(3600*24)/3600), '时', FLOOR((secondcount%3600)/60), '分', FLOOR(secondcount%3600%60), '秒') workTime
FROM(
	SELECT t.id,start_time,end_time,
	(
		TIMESTAMPDIFF(SECOND,start_time,end_time) - (select count(*) from sys_holiday where date < end_time and date > start_time) * 24 * 60 * 60
	) 
	- TIMESTAMPDIFF(SECOND, start_time, IFNULL(DATE_ADD(a.date,INTERVAL 1 DAY), start_time)) 
	+ TIMESTAMPDIFF(SECOND, end_time, IFNULL(DATE_ADD(b.date,INTERVAL 1 DAY), end_time)) as secondcount 
	FROM test t
	LEFT JOIN sys_holiday a on date(start_time) = a.date
	LEFT JOIN sys_holiday b on date(DATE_SUB(end_time, INTERVAL 1 SECOND)) = b.date
) z

SQL解释

SELECT id,start_time,end_time,secondcount,
CONCAT(FLOOR(secondcount/(3600*24)), '天', FLOOR(secondcount%(3600*24)/3600), '时', FLOOR((secondcount%3600)/60), '分', FLOOR(secondcount%3600%60), '秒') workTime
FROM(

	-- 结果 = 用总时间差 - 节假日的天数 -（开始时间是假期）开始时间和第二天0点的差值 + （结束时间是假期）结束时间和第二天0点的差值
	SELECT t.id,start_time,end_time,
	(
		-- 这里是计算开始时间和结束时间之间的总时间差
		TIMESTAMPDIFF(SECOND,start_time,end_time)
		
		-- 这里是获取开始时间和结束时间之间的节假日的天数，再将天数直接转成秒数
		- (select count(*) from sys_holiday where date < end_time and date > start_time) * 24 * 60 * 60
	) 
	-- 如果开始时间这天是假期的话，计算它的时间和第二天0点之间的差值
	- TIMESTAMPDIFF(SECOND, start_time, IFNULL(DATE_ADD(a.date,INTERVAL 1 DAY), start_time)) 
	
	-- 如果结束时间这天是假期的话，计算它的时间和第二天0点之间的差值
	+ TIMESTAMPDIFF(SECOND, end_time, IFNULL(DATE_ADD(b.date,INTERVAL 1 DAY), end_time)) as secondcount 
	FROM test t
	LEFT JOIN sys_holiday a on date(start_time) = a.date -- start_time格式到天和节假日表关联，如果 a.date 不是null，说明start_time这一天是节假日
	LEFT JOIN sys_holiday b on date(DATE_SUB(end_time, INTERVAL 1 SECOND)) = b.date  -- end_time格式到天和节假日表关联，如果 b.date 不是null，说明end_time这一天是节假日
	
) z

为什么开始时间是假期就要减去，而结束时间是假期就要加上呢？
(select count(*) from sys_holiday where date < end_time and date > start_time) 这一句就是查询 (开始时间 < date < 结束时间) 这个范围的天数。

因为每个假期都是从这一天的0点开始的。

比如开始时间是2022-11-12 12:10:11，结束时间是2022-11-13 12:10:11，这两天都是周六日，但这句查询查的天数只有一天，就是13号这天；
因此，开始时间是假期的话，它后面的时间一直到第二天0点，都需要减去；
而结束时间是假期的话，它后面的时间一直到第二天0点，是不算在节假日里面的（因为后面的时间不在开始和结束时间这个范围），前面是算的13号这一整天的秒数，实际上12:10:11以后的时间，是不在区间范围的，所以要把这个非范围内的秒数给重新加上去。

在这里插入图片描述
PS：上面这张图是旧图，和新增改的有点不一样了。注意一下。

这整句计算的SQL大概是这么个意思，不理解的可以把我标注的1、2、3这几句单独拿出来查询一遍看看，再照着我的解释理一遍。

执行结果

在这里插入图片描述

校验

SELECT TIMESTAMPDIFF(SECOND,'2021-12-01 12:50:27','2021-12-04 00:00:00') id1, -- 212973
	   TIMESTAMPDIFF(SECOND,'2021-12-03 08:22:11','2021-12-04 00:00:00')+TIMESTAMPDIFF(SECOND,'2021-12-06 00:00:00','2021-12-06 17:54:19') id2, -- 120728
	   TIMESTAMPDIFF(SECOND,'2022-01-10 07:20:06','2022-01-11 22:55:13') id5, -- 142507
	   TIMESTAMPDIFF(SECOND,'2021-12-27 00:00:00','2021-12-28 16:55:25') id6  -- 147325

在这里插入图片描述

传入两个日期作为参数，计算它们的差值；

set @startTime='2023-01-01 00:00:00';
set @endTime='2023-01-03 00:10:00';

SELECT *,总时差 - 节假日的秒数 - 开始时间的差值 + 结束时间的差值 AS 结果
FROM(
	SELECT TIMESTAMPDIFF(SECOND,@startTime,@endTime) 总时差,

	(select count(*) from sys_holiday where date < @endTime and date > @startTime) * 24 * 60 * 60 AS 节假日的秒数,

	CASE WHEN date(@startTime)=(SELECT date FROM sys_holiday WHERE date(@startTime)=date) 
	THEN TIMESTAMPDIFF(SECOND, @startTime, DATE_ADD(date(@startTime),INTERVAL 1 DAY)) ELSE 0 END  开始时间的差值,

	CASE WHEN date(@endTime)=(SELECT date FROM sys_holiday WHERE date(DATE_SUB(@endTime, INTERVAL 1 SECOND))=date) 
	THEN TIMESTAMPDIFF(SECOND, @endTime, DATE_ADD(date(@endTime),INTERVAL 1 DAY)) ELSE 0 END  结束时间的差值
) a

@startTime、@endTime就是传进来的开始、结束时间，用这两个时间在 sys_holiday 节假日表直接按条件查询即可。总时差、节假日的秒数、开始时间的差值、结束时间的差值 这四个值的计算方式和前面的一样，就是把前面的那个查询语句拆开来了而已，它们的意思也是和我上面解释的一样。