计算两个日期之间的天数，不包括周末（仅限 MySQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9757919/
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
Count days between two dates, excluding weekends (MySQL only)
提问by lorenzo-s
I need to calculate the difference (in days) between two dates in MySQL excluding weekends (Saturday and Sunday). That is, the difference in days minus the number of Saturday and Sunday in between.
我需要计算 MySQL 中两个日期之间的差异（以天为单位），不包括周末（周六和周日）。也就是说，天数减去两者之间周六和周日的数量。
At the moment, I simply count the days using:
目前，我只是使用以下方法计算天数：
SELECT DATEDIFF('2012-03-18', '2012-03-01')
This return 17, but I want to exclude weekends, so I want 12(because the 3rd and 4th, 10th and 11th and 17th are weekends days).
这个返回17，但我想排除周末，所以我想要12（因为第 3 天和第 4 天，第 10 天和第 11 天和第 17 天是周末）。
I do not know where to start. I know about the WEEKDAY()function and all related ones, but I do not know how to use them in this context.
我不知道从哪里开始。我知道这个WEEKDAY()函数和所有相关的函数，但我不知道如何在这种情况下使用它们。
回答by pollux1er
Simply try it using a simple function :
只需使用一个简单的函数尝试一下：
CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
- ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
- (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
- (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);
Test :
测试 ：
SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;
Result :
结果 ：
| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
| 13 | 13 |
回答by biziclop
Illustration:
插图：
mtwtfSSmtwtfSS
123456712345 one week plus 5 days, you can remove whole weeks safely
12345------- you can analyze partial week's days at start date
-------12345 or at ( end date - partial days )
Pseudocode:
伪代码：
@S = start date
@E = end date, not inclusive
@full_weeks = floor( ( @E-@S ) / 7)
@days = (@E-@S) - @full_weeks*7 OR (@E-@S) % 7
SELECT
@full_weeks*5 -- not saturday+sunday
+IF( @days >= 1 AND weekday( S+0 )<=4, 1, 0 )
+IF( @days >= 2 AND weekday( S+1 )<=4, 1, 0 )
+IF( @days >= 3 AND weekday( S+2 )<=4, 1, 0 )
+IF( @days >= 4 AND weekday( S+3 )<=4, 1, 0 )
+IF( @days >= 5 AND weekday( S+4 )<=4, 1, 0 )
+IF( @days >= 6 AND weekday( S+5 )<=4, 1, 0 )
-- days always less than 7 days
回答by Vinod Cyriac
Below function will give you the Weekdays, Weekends, Date difference with proper results:
You can call the below function like,
select getWorkingday('2014-04-01','2014-05-05','day_diffs');
select getWorkingday('2014-04-01','2014-05-05','work_days');
select getWorkingday('2014-04-01','2014-05-05','weekend_days');
DROP FUNCTION IF EXISTS PREPROCESSOR.getWorkingday;
CREATE FUNCTION PREPROCESSOR.`getWorkingday`(d1 datetime,d2 datetime, retType varchar(20)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE dow1, dow2,daydiff,workdays, weekenddays, retdays,hourdiff INT;
declare newstrt_dt datetime;
SELECT dd.iDiff, dd.iDiff - dd.iWeekEndDays AS iWorkDays, dd.iWeekEndDays into daydiff, workdays, weekenddays
FROM (
SELECT
dd.iDiff,
((dd.iWeeks * 2) +
IF(dd.iSatDiff >= 0 AND dd.iSatDiff < dd.iDays, 1, 0) +
IF (dd.iSunDiff >= 0 AND dd.iSunDiff < dd.iDays, 1, 0)) AS iWeekEndDays
FROM (
SELECT dd.iDiff, FLOOR(dd.iDiff / 7) AS iWeeks, dd.iDiff % 7 iDays, 5 - dd.iStartDay AS iSatDiff, 6 - dd.iStartDay AS iSunDiff
FROM (
SELECT
1 + DATEDIFF(d2, d1) AS iDiff,
WEEKDAY(d1) AS iStartDay
) AS dd
) AS dd
) AS dd ;
if(retType = 'day_diffs') then
set retdays = daydiff;
elseif(retType = 'work_days') then
set retdays = workdays;
elseif(retType = 'weekend_days') then
set retdays = weekenddays;
end if;
RETURN retdays;
END;
Thank You.
Vinod Cyriac.
Bangalore
回答by Raja Rama Mohan Thavalam
IT my helpful to you
我对你有帮助
The bellow logic only show the how many days like
波纹管逻辑只显示多少天像
sun mon
1 2 .....................
DELIMITER $$
DROP FUNCTION IF EXISTS `xx`.`get_weekday` $$
CREATE FUNCTION `xx`.`get_weekday` (first_date date, last_date date, curr_week_day int) RETURNS INT
BEGIN
DECLARE days_tot int;
DECLARE whole_weeks int;
DECLARE first_day int;
DECLARE last_day int;
SET whole_weeks = FLOOR(DATEDIFF(last_date,first_date)/7) ;
SET first_day = WEEKDAY(first_date) ;
SET last_day = WEEKDAY(last_date) ;
IF curr_week_day BETWEEN first_day AND last_day
AND last_day > first_day
OR ( curr_week_day BETWEEN last_day AND first_day
AND last_day < first_day )
THEN SET days_tot = whole_weeks + 1;
ELSE SET days_tot = whole_weeks ;
END IF;
RETURN days_tot;
END $$
DELIMITER ;
SELECT
`xx`.`get_weekday` ('2009-01-01', '2009-07-20', 0) as mo,
`xx`.`get_weekday` ('2009-01-01', '2009-07-20', 1) as tu,
`xx`.`get_weekday` ('2009-01-01', '2009-07-20', 2) as we,
`xx`.`get_weekday` ('2009-01-01', '2009-07-20', 3) as th,
`xx`.`get_weekday` ('2009-01-01', '2009-07-20', 4) as fr,
`xx`.`get_weekday` ('2009-01-01', '2009-07-20', 5) as sa,
`xx`.`get_weekday` ('2009-01-01', '2009-07-20', 6) as su;
Table based query
基于表的查询
ip:
网络地址：
Weekday count
2 10
3 5
SELECT WEEKDAY( `req_date_time` ) AS weekday, COUNT( id ) AS id
FROM `ddd`
WHERE (
`req_date_time` >= '2014-12-01'
AND `req_date_time` <= '2014-12-31'
)
AND WEEKDAY( `req_date_time` ) != '1'
GROUP BY WEEKDAY( `req_date_time` )
回答by user3646932
You can also do this with Queries, but you will need a dates table that covers the date ranges. It's good practice to create a dates tables for use in all your projects, anyways.
您也可以使用查询来执行此操作，但您需要一个涵盖日期范围的日期表。无论如何，创建一个用于所有项目的日期表是一种很好的做法。
To create the dates table, all you do is generate a long list of dates (EXCEL is convenient method, but there are other ways) and import them into a table. Then, use those dates in conjunction with various date functions to derive the 'day of week', 'month', 'year', etc, and save all that into the table with the dates, like so:
要创建日期表，您要做的就是生成一长串日期（EXCEL 是一种方便的方法，但还有其他方法）并将它们导入到表中。然后，将这些日期与各种日期函数结合使用以导出“星期几”、“月份”、“年份”等，并将所有这些与日期一起保存到表格中，如下所示：
tbl_dates
tbl_dates
dow is 'day of week' in my table. Then your query looks like this:
道琼斯指数是我表中的“星期几”。然后您的查询如下所示：
SELECT Count(theDate) AS numWeekDays
FROM tbl_dates
WHERE theDate >[startDate] And theDate <=[endDate] AND dow <> 1 AND dow <> 7;
In this case, 1 and 7 are Sunday, Saturday, respectively (which is the default) and, of course, you can nest that into another query if you need to calculate this for many startDate(s) and endDate(s).
在这种情况下，1 和 7 分别是星期日和星期六（这是默认值），当然，如果您需要为许多 startDate(s) 和 endDate(s) 计算它，您可以将其嵌套到另一个查询中。
