如何在MySQL中统计不含周末和节假日的日期差异
如何在MySQL中统计不含周末和节假日的日期差异
提问于 2012-04-26 17:38:39
我需要计算两个日期之间的天数(工作日)，不包括周末(最重要的)和节假日
SELECT DATEDIFF(end_date, start_date) from accounts但是，我不知道我应该如何在MySQL中做到这一点，我找到了这篇文章Count days between two dates, excluding weekends (MySQL only)。我不知道如何在mysql中实现函数查询，你能给出一些如何用mysql查询来实现的信息吗？如果我遗漏了什么，请告诉我。
编辑
CREATE TABLE `candidatecase` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
`CreatedBy` int(11) NOT NULL,
`UseraccountID` int(11) NOT NULL COMMENT 'User Account ID',
`ReportReadyID` int(11) DEFAULT NULL COMMENT 'Report Ready ID',
`DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Date Created',
`InitiatedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Initiated',
`ActualCompletedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Completed Case',
`ProjectedCompletedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Projected Finish',
`CheckpackagesID` int(11) DEFAULT NULL COMMENT 'Default Check Package Auto Assign Once Initiate Start',
`Alacartepackage1` int(11) DEFAULT NULL COMMENT 'Ala carte Request #2',
`Alacartepackage2` int(11) DEFAULT NULL COMMENT 'Ala carte Request #3',
`OperatorID` int(11) NOT NULL COMMENT 'User Account - Operator',
`Status` int(11) NOT NULL COMMENT 'Status',
`caseRef` varchar(100) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=293 ;
--
-- Dumping data for table `candidatecase`
--
INSERT INTO `candidatecase` (`ID`, `CreatedBy`, `UseraccountID`, `ReportReadyID`, `DateCreated`, `InitiatedDate`, `ActualCompletedDate`, `ProjectedCompletedDate`, `CheckpackagesID`, `Alacartepackage1`, `Alacartepackage2`, `OperatorID`, `Status`, `caseRef`) VALUES
(1, 43, 70, NULL, '2011-07-22 02:29:31', '2011-07-07 07:27:44', '2011-07-22 02:29:31', '2011-07-17 06:53:52', 11, NULL, NULL, 44, 6, ''),
(2, 43, 74, NULL, '2012-04-03 04:17:15', '2011-07-11 07:07:23', '2011-07-13 05:32:58', '2011-07-21 07:01:34', 20, 0, 0, 51, 0, ''),
(3, 43, 75, NULL, '2011-07-29 04:10:07', '2011-07-11 07:27:12', '2011-07-29 04:10:07', '2011-07-21 07:02:14', 20, NULL, NULL, 45, 6, ''),
(4, 43, 78, NULL, '2011-07-18 03:32:27', '2011-07-11 07:51:31', '2011-07-13 02:18:34', '2011-07-21 07:37:53', 20, NULL, NULL, 45, 6, ''),
(5, 43, 76, NULL, '2011-07-29 04:09:19', '2011-07-11 07:51:11', '2011-07-29 04:09:19', '2011-07-21 07:38:30', 20, NULL, NULL, 45, 6, ''),
(6, 43, 77, NULL, '2011-07-18 03:32:49', '2011-07-11 07:51:34', '2011-07-18 02:18:46', '2011-07-21 07:39:00', 20, NULL, NULL, 45, 6, ''),
(7, 43, 79, NULL, '2011-07-18 03:33:02', '2011-07-11 07:53:24', '2011-07-18 01:50:12', '2011-07-21 07:42:57', 20, NULL, NULL, 45, 6, ''),
(8, 43, 80, NULL, '2011-07-29 04:10:38', '2011-07-11 07:53:58', '2011-07-29 04:10:38', '2011-07-21 07:43:14', 20, NULL, NULL, 45, 6, ''),
(9, 43, 81, NULL, '2011-07-18 03:31:54', '2011-07-11 07:53:49', '2011-07-13 02:17:02', '2011-07-21 07:43:43', 20, NULL, NULL, 45, 6, ''),
(11, 43, 88, NULL, '2011-07-18 03:15:53', '2011-07-13 04:57:38', '2011-07-15 08:57:15', '2011-07-23 04:39:14', 12, NULL, NULL, 44, 6, ''),
(13, 43, 90, NULL, '2011-07-26 07:39:24', '2011-07-13 12:16:48', '2011-07-26 07:39:24', '2011-07-23 12:13:50', 15, NULL, NULL, 51, 6, ''),
(63, 43, 176, NULL, '2011-09-13 08:23:13', '2011-08-26 10:00:32', '2011-09-13 08:23:13', '2011-09-05 09:58:47', 41, NULL, NULL, 45, 6, ''),
(62, 43, 174, NULL, '2011-08-24 03:54:30', '2011-08-24 03:53:13', '2011-08-24 03:54:30', '2011-08-29 03:52:48', 17, NULL, NULL, 51, 6, ''),
(61, 43, 173, NULL, '2011-08-24 03:55:05', '2011-08-24 03:53:39', '2011-08-24 03:55:05', '2011-08-29 03:52:36', 17, NULL, NULL, 51, 6, ''),
(60, 43, 172, NULL, '2011-08-24 03:22:41', '2011-08-24 03:21:50', '2011-08-24 03:22:41', '2011-08-29 03:21:11', 17, NULL, NULL, 51, 6, ''),
(59, 43, 171, NULL, '2011-08-24 03:23:19', '2011-08-24 03:22:00', '2011-08-24 03:23:19', '2011-08-29 03:20:57', 17, NULL, NULL, 51, 6, '');回答 3
Stack Overflow用户
发布于 2012-04-26 18:06:02
创建一个表，其中包含接下来100年的所有周末和节假日。
你需要能够指定一天什么时候是“假日”，因为还没有人知道2052年的假日是什么，你无论如何都不能在这个时候做出一个准确的函数。每年知道节假日时，只需更新您的非工作日表格(但您将始终知道周末)。
那么你的查询就变成：
SELECT DATEFIFF(end_date, start_date) - COALESCE((SELECT COUNT(1) FROM nonWorkDays WHERE nonWorkDays.date BETWEEN start_date AND end_date), 0)
FROM accounts如果你真的需要编写一个DATEDIFFWITHOUTWEEKENDSORHOLIDAYS函数，那么只需使用上面的代码并创建一个函数(在每个关系型数据库中都有大量关于如何创建函数的资源)。只是一定要给它起个更好的名字。^_^
您需要修复的一件事是，我认为上面的某个地方缺少+1，例如DATEDIFF( today，today)如果今天是周末，将返回-1而不是0。
Stack Overflow用户
发布于 2012-04-26 18:15:34
像这样的东西可能行得通。将所有假日日期和周末日期添加到表中。
SELECT
DATEDIFF(end_date, start_date)
FROM table
WHERE date NOT IN (SELECT date FROM holidaydatestable )Stack Overflow用户
发布于 2012-04-26 17:47:31
创建一个函数，它将在日期之间循环一段时间，当不是周六或周日时，递增天数。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10330836
复制相关文章
相似问题