如何计算 MySQL 中不包括周末和节假日的日期差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10330836/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count date difference excluding weekend and holidays in MySQL
提问by Akram
I need to count days (business days) between two dates excluding weekend (most important) and holidays
我需要计算两个日期之间的天数（工作日），不包括周末（最重要的）和假期
SELECT DATEDIFF(end_date, start_date) from accounts
But, I don't know how am I supposed to do it in MySQL, I found this article Count days between two dates, excluding weekends (MySQL only). I cannot figure out how to functional query in mysql, Can you give some information of how can achieve this with mysql query. If I am missing anything let me know.
但是，我不知道我应该如何在 MySQL 中做到这一点，我发现这篇文章计算两个日期之间的天数，不包括周末（仅限 MySQL）。我无法弄清楚如何在 mysql 中进行功能查询，您能否提供一些有关如何使用 mysql 查询实现这一点的信息。如果我遗漏了什么，请告诉我。
[EDIT]
[编辑]
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, '');
回答by Thorsten
You might want to try this:
你可能想试试这个：
Count the number of working days (took it from here)
SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1)This gives you 261 working days for 2012.
Now you need to know your holidays that are not on a weekend
SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6The result of this depends on your holiday table.
We need to get that in one query:
SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1) - (SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6)This should be it.
计算工作日数（取自这里）
SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1)这为您提供了 2012 年的 261 个工作日。
现在你需要知道你的假期不是周末
SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6结果取决于您的假期表。
我们需要在一个查询中得到它：
SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1) - (SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6)应该是这样。
Edit: Please be aware that this only works properly if your end date is higher than your start date.
编辑：请注意，这仅在您的结束日期高于开始日期时才能正常工作。
回答by Seph
Create a table that contains all the weekends and holidays for the next 100whatever years.
创建一个表，其中包含接下来 100 年的所有周末和假期。
You need to be able to specify when a day is a 'holiday' given that no one knows what the holidays will be for 2052 yet, you will not be able to make an accurate function at this time anyway. just update your non-work day table each year when the holidays become known (but you will always know the weekends).
鉴于没有人知道 2052 年的假期是什么时候，您需要能够指定一天是“假期”，无论如何，此时您将无法做出准确的函数。每年在知道假期时更新您的非工作日表（但您将始终知道周末）。
Then your query becomes:
然后你的查询变成：
SELECT DATEFIFF(end_date, start_date) - COALESCE((SELECT COUNT(1) FROM nonWorkDays WHERE nonWorkDays.date BETWEEN start_date AND end_date), 0)
FROM accounts
If you really need to write a DATEDIFFWITHOUTWEEKENDSORHOLIDAYSfunction then just use the above and create a function (there's plenty of resources on how to make functions in each RDBMS).. just be sure to give it a better name. ^_^
如果您真的需要编写一个DATEDIFFWITHOUTWEEKENDSORHOLIDAYS函数，那么只需使用上面的方法并创建一个函数（关于如何在每个 RDBMS 中创建函数的资源有很多）.. 一定要给它一个更好的名字。^_^
One thing you will need to fix is I think there's a +1 missing somewhere in the above, for example DATEDIFF(today, today) if today is a weekend will return -1 instead of returning 0.
您需要解决的一件事是我认为上面的某个地方缺少 +1，例如 DATEDIFF(today, today) 如果今天是周末将返回 -1 而不是返回 0。
回答by Robbo_UK
Something like this may work. Add all holiday dates and weekend dates to a table.
像这样的事情可能会奏效。将所有假期日期和周末日期添加到表中。
SELECT
DATEDIFF(end_date, start_date)
FROM table
WHERE date NOT IN (SELECT date FROM holidaydatestable )
回答by Tanmay Nehete
Try This Code this will Calculate no of days Excluding Weekends
试试这个代码，这将计算不包括周末的天数
SELECT
(DATEDIFF(dd, @StartDate, @EndDate)+1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
from test_tbl where date NOT IN (SELECT date FROM holidaydatestable )
回答by aF.
Make a function that will make a while cycle between the dates incrementing the number of days when it's not a saturday or sunday.
制作一个函数，当它不是星期六或星期日时，它会在日期之间循环一段时间，增加天数。
