MySQL 함수는 두 날짜 사이의 근무 일수를 구합니다.
Excel에는 NETWORKDAYS() 함수가 있어 두 날짜 사이의 영업일수를 알 수 있습니다.
MySQL과 비슷한 기능을 가진 사람이 있나요?휴일은 복잡함을 더하기 때문에 해결책은 휴일을 처리할 필요가 없습니다.
이 표현은 -
5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
는 시작일 @S와 종료일 @E 사이의 영업일수를 계산합니다.
종료일(@E)이 시작일(@S) 이전이 아니라고 가정합니다.시작일과 종료일이 같으면 영업일이 0일이라는 점에서 DATIFF와 호환됩니다.휴일을 무시합니다.
자릿수의 문자열은 다음과 같이 구성됩니다.시작일과 종료일의 테이블을 만듭니다.행은 월요일(WEKday 0)로 시작하고 열도 월요일로 시작해야 합니다.왼쪽 위에서 오른쪽 아래까지의 대각선에 모두 0을 입력합니다(즉, 월요일과 월요일, 화요일과 화요일 사이에는 0영업일이 있습니다).각 요일에 대해 대각선(항상 0이어야 함)에서 시작하고 오른쪽에 있는 열을 한 번에 하나씩 입력합니다.주말(영업일 외)란에 착륙할 경우 영업일수는 변경되지 않고 왼쪽부터 이동됩니다.그렇지 않으면 영업일수가 1일 늘어납니다.행 루프의 끝에 도달하면 같은 행의 선두로 돌아가 다시 대각선에 도달할 때까지 계속합니다.그럼 다음 행으로 넘어가세요.
예: 토요일과 일요일이 영업일이 아닌 경우 -
| M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0
다음으로 테이블의 49 값을 문자열에 연결합니다.
버그가 발견되면 알려주세요.
- 개선된 표 편집:
| M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0
개선된 문자열: '0123444401233401222234011234001234000123440'
개선된 표현:
5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
제안된 문자열이 잘못되었을 수 있습니까?
DATDIFF(from, to)는 'to'를 제외합니다.같은 방법으로 이 문자열도 다음과 같이 해야 합니다.
월요일 -> 금요일 = {월, 투, 수, 목} = 4
월요일 -> 토요일 = {월, 투, 수, 목, 금} = 5
화요일 -> 월요일 = {Tu, 수요일, Th, 금요일, 토요일 건너뛰기, 일몰 건너뛰기, 월 제외} = 4
등등
제안 매트릭스:
| M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 4 0 1 2 3 4 4
W| 3 4 0 1 2 3 3
T| 2 3 4 0 1 2 2
F| 1 2 3 4 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0
문자열: '0123455401234434012332340122123401101234000123450'
내가 뭘 빠트렸나요?:)
이 솔루션은 기본적으로 Rodger와 동일한 접근 방식을 사용합니다. 다만 매트릭스를 생성하는 방법은 훨씬 복잡합니다.주의: 이 솔루션의 출력은 NETWORKDAYS와 호환되지 않습니다.
Rodger의 솔루션과 마찬가지로, 이 값은 저장 프로시저를 정의할 필요 없이 시작일(@S)과 종료일(@E) 사이의 영업일수를 계산합니다.종료일이 시작일 이전이 아닌 것으로 가정합니다.같은 시작일과 종료일을 사용하면 0이 생성됩니다.휴일은 고려되지 않습니다.
이것과 Rodger의 솔루션의 큰 차이점은 행렬과 결과적인 숫자의 문자열은 내가 포함하지 않은 복잡한 알고리즘에 의해 구성된다는 것이다.이 알고리즘의 출력은 단위 테스트에 의해 검증됩니다(아래 테스트 입력 및 출력 참조).이 매트릭스에서 주어진 x와 y 값 쌍(WEEKday(@S)과 WEOKDay(@E))의 교집합은 두 값 간의 근무일 차이를 산출합니다.배치 순서는 위치를 표시하기 위해 두 가지를 합산하기 때문에 실제로 중요하지 않습니다.
영업일은 월요일부터 금요일까지입니다.
| M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 5 0 1 2 3 4 4
W| 4 5 0 1 2 3 3
T| 3 4 5 0 1 2 2
F| 2 3 4 5 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0
테이블 내의 49개의 값은 다음 문자열로 연결됩니다.
0123455501234445012333450122234501101234000123450
마지막으로 올바른 표현은 다음과 같습니다.
5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
이 솔루션을 사용하여 다음 입력 및 출력을 확인했습니다.
Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0
Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Monday, 2012-09-10 = 10
Monday, 2012-08-27 -> Monday, 2012-09-17 = 15
Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16
Monday, 2012-08-27 -> Monday, 2012-09-24 = 20
Monday, 2012-08-27 -> Monday, 2012-10-01 = 25
Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1
Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1
Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1
Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1
Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0
Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0
Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1
Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1
Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1
Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1
Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1
Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0
Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10
Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0
Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0
Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0
Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1
Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2
Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3
Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4
Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0
Monday, 2012-08-27 -> Monday, 2012-08-27 = 0
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2
Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3
Monday, 2012-08-27 -> Friday, 2012-08-31 = 4
Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5
Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5
공휴일을 추적해야 하므로 달력 테이블이 적절할 수 있습니다.
CREATE TABLE Calendar
(
calendar_date DATETIME NOT NULL,
is_holiday BIT NOT NULL,
is_weekend BIT NOT NULL,
CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)
물론 응용 프로그램에서 작업할 수 있는 모든 기간의 날짜를 입력해야 합니다.1년에 365일(또는 366)밖에 없기 때문에 1900년에서 2100년으로 가는 것은 큰 문제가 되지 않습니다.휴일뿐만 아니라 모든 날짜로 로딩해야 합니다.
이 시점에서 필요한 질문은 다음과 같습니다.
SELECT
COUNT(*)
FROM
Calendar
WHERE
calendar_date BETWEEN '2009-01-01' AND '2009-10-01' AND
is_holiday = 0 AND
is_weekend = 0
주의: 저는 주로 MS SQL을 사용하고 있으며 MySQL을 오랫동안 사용하지 않았기 때문에 위의 사항을 수정해야 할 수 있습니다.예를 들어 MySQL에 BIT 데이터형이 있는지조차 기억나지 않습니다.
더 참고하기 위해서입니다.위의 어느 것도 나에게 효과가 없었지만 @Jeff Kooser의 수정 버전:
SELECT (DATEDIFF(date_end, date_start)) -
((WEEK(date_end) - WEEK(date_start)) * 2) -
(case when weekday(date_end) = 6 then 1 else 0 end) -
(case when weekday(date_start) = 5 then 1 else 0 end) -
(SELECT COUNT(*) FROM holidays WHERE holiday>=date_start and holiday<=data_end)
매월 첫째 날이 지정되면 해당 달 내의 평일 수가 반환됩니다.MySQL에서.저장 프로시저 없이.
SELECT (DATEDIFF(LAST_DAY(?),?) + 1) -
((WEEK(LAST_DAY(?)) - WEEK(?)) * 2) -
(case when weekday(?) = 6 then 1 else 0 end) -
(case when weekday(LAST_DAY(?)) = 5 then 1 else 0 end)
위의 야다 씨의 함수를 바탕으로 현재 날짜(포함하지 않음)에서 목표 날짜까지의 남은 근무 일수를 계산하는 주제에 대한 약간의 변화를 소개합니다.또한 이스라엘에서의 다른 주말도 처리합니다:-) 목표일이 과거일 경우 부정적인 결과가 발생한다는 점에 유의하십시오(그것이 바로 제가 원하던 것입니다.
DELIMITER //
DROP FUNCTION IF EXISTS WORKDAYS_LEFT//
CREATE FUNCTION WORKDAYS_LEFT(target_date DATE, location char(2))
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE check_date DATE;
DECLARE diff INT;
DECLARE extra_weekend_days INT;
DECLARE weeks_diff INT;
SET start_date = CURDATE();
SET end_date = target_date;
SET diff = DATEDIFF(end_date, start_date);
SET weeks_diff = FLOOR(diff / 7);
SET end_date = DATE_SUB(end_date, INTERVAL (weeks_diff * 7) DAY);
SET check_date = DATE_ADD(start_date, INTERVAL 1 DAY);
SET extra_weekend_days = 0;
WHILE check_date <= end_date DO
SET extra_weekend_days = extra_weekend_days +
IF(DAYNAME(check_date) = 'Saturday', 1, 0) +
IF(DAYNAME(check_date) = IF(location = 'IL','Friday', 'Sunday'), 1, 0);
SET check_date = DATE_ADD(check_date, INTERVAL 1 DAY);
END WHILE;
RETURN diff - weeks_diff*2 - extra_weekend_days;
END//
DELIMITER ;
SELECT 5* (DATEDIFF(u.EndDate, u.StartDate) DIV 7) + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(u.StartDate) + WEEKDAY(u.EndDate) + 1, 1)
이 경우 다음 경우를 고려해야 합니다.
1) startdate = enddate, duration = 1 및 이와 유사한 경우.
가장 많이 투표된 답변에 언급된 논리로 문자열을 계산하여 필요한 결과를 얻었습니다.
자, 소년소녀 여러분, 분명히 가장 좋은 해결책이 있습니다. 여기 두 날짜 사이의 평일 수를 알 수 있는 간단한 선택 문구가 있습니다.
select
FLOOR(DATEDIFF(later_date, earlier_date) / 7) * 5 +
least(DATEDIFF(later_date, earlier_date) % 7, 5) +
if(weekday(later_date) < weekday(earlier_date), -2, 0);
간단한 설명
- 완주수를 구해서 평일에 5를 곱하다
- 남은 일주일의 일수를 얻다
- 남은 한 주가 주말을 넘나들면 주말 2일을 빼라.
야다의 솔루션이 제대로 작동하지 않습니다.변경 사항:
DELIMITER $$
DROP FUNCTION IF EXISTS `catalog`.`WORKDAYS` $$
CREATE FUNCTION `catalog`.`WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
SET diff = DATEDIFF(end_date, start_date);
RETURN (CASE WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Saturday' THEN diff
WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Sunday' THEN (diff - 2)
WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Sunday' THEN (diff - 1)
WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Saturday' THEN (diff + 1)
WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) in ('Saturday', 'Sunday') THEN (diff + 1)
WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff -1)
WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff + 1)
WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) not in ('Saturday', 'Sunday')
&& WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2)
ELSE diff END)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END);
END $$
DELIMITER ;
이 쿼리는 주말을 제외한 두 날짜 사이의 근무 일 수를 쉽게 반환합니다.
select datediff('2016-06-19','2016-06-01') - (floor(datediff('2016-06-19','2016-06-01')/6) + floor(datediff('2016-06-19','2016-06-01')/7));
저는 이 요건을 가지고 특정 국가의 주말과 공휴일을 피하면서 계산할 수 있는 완전한 함수를 작성했습니다(별도의 표를 사용).블로그(http://mgw.dumatics.com/mysql-function-to-calculate-elapsed-working-time/)에 모든 기능과 자세한 내용을 게재하고, 설명과 흐름도, 홀리데이 테이블 작성 등...기꺼이 여기에 놓고 싶지만 너무 길어서...
해결된 문제의 예:
"2016년 6월 10일 금요일 12시"에 09:00~16:00 사이에 열리는 "영국" 사이트의 사건이 기록되었다고 가정해 보겠습니다.이 사건은 "2016년 6월 14일 화요일 14:00"에 종결되었습니다.
위의 사고에서는 960분=16시간=[금요일 (12:00~16:00)4시간+월요일 (09:00~16:00)7시간+화요일 (09:00~14:00)5시간]으로 연령을 계산해야 합니다.
주말의 존재를 진정으로 무시하고 싶다면, 토요일/일요일의 시작은 월요일의 시작인 것처럼, 토요일/일요일의 끝은 금요일의 끝인 것처럼 취급해야 합니다.그러므로 주말에 시작하고 끝나는 것은 시작과 끝을 모두 무시해야 한다.다른 대답들은 다 그런 것 같지 않아요.
다음 함수가 이를 수행합니다.
CREATE DEFINER=`root`@`localhost` FUNCTION `weekdayDiff`
(
edate datetime,
sdate datetime
)
RETURNS int
DETERMINISTIC
BEGIN
if edate>sdate
then
return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID('+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1', 2*(7 * WEEKDAY(sdate) + WEEKDAY(edate)) + 1, 2);
else
return -(5 * (DATEDIFF(sdate, edate) DIV 7) + MID('+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1', 2*(7 * WEEKDAY(edate) + WEEKDAY(sdate)) + 1, 2));
end if;
-- The following works unless both start and finish date are on weekends.
-- return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(sdate) + WEEKDAY(edate) + 1, 1);
END;
Rodger의 답변 언어로 위의 문자열을 작성한 표는 다음과 같습니다(토요일/일요일 시작 및 종료 시 0이 아닌 -1인 경우만 다릅니다).
| M T W T F S S
-|---------------------
M| +0 +1 +2 +3 +4 +4 +4
T| +4 +0 +1 +2 +3 +3 +3
W| +3 +4 +0 +1 +2 +2 +2
T| +2 +3 +4 +0 +1 +1 +1
F| +1 +2 +3 +4 +0 +0 +0
S| +0 +1 +2 +3 +4 -1 -1
S| +0 +1 +2 +3 +4 +4 -1
상위 답변은 시작일과 종료일 사이의 일수로 계산되지만 종료일은 제외됩니다.
또한 2018-05-05 토요일부터 2018-05-12 토요일까지 같은 주말에 시작하고 끝나는 날짜의 경우 하루 더 계산했습니다.
나에게 딱 맞는 기능이 있어!
drop procedure if exists get_duration$$
create procedure get_duration(in data_from date, in data_to date)
begin
if (WEEKDAY(data_from) = 5 AND WEEKDAY(data_to) = 5)
OR (WEEKDAY(data_from) = 6 AND WEEKDAY(data_to) = 6) then
select (5 * (DATEDIFF(data_to, data_from) DIV 7)
+ MID('0123444401233334012222340111123400001234000123440',
7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1)) dur;
else
select (5 * (DATEDIFF(data_to, data_from) DIV 7)
+ MID('0123444401233334012222340111123400001234000123440',
7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1))+1 dur;
end if;
end$$
"휴일 무시" 파에서 겪게 될 문제는 나라마다 휴일이 다르다는 것입니다.
먼저 해당 국가의 공휴일을 정의하고 특정 날짜가 공휴일인지 확인하기 위해 해당 공휴일을 통과해야 합니다.
mysql에서 당신이 원하는 기능을 하는 일반적인 함수는 모릅니다.
미안!
주말 이외의 요일은 다음과 같이 차이가 날 수 있습니다.
CREATE FUNCTION `WDDIFF` (d0 DATE, d1 DATE)
RETURNS INT DETERMINISTIC
COMMENT 'Date0, Date1'
BEGIN
RETURN DATEDIFF(d1, d0) - (DATEDIFF(DATE_SUB(d1, INTERVAL WEEKDAY(d1) DAY), DATE_ADD(d0, INTERVAL (7 - WEEKDAY(d0)) DAY))/7+1)*2 + IF(WEEKDAY(d0)>4, 1, 0) + 1;
END
사용방법: 월초 이후 주
SELECT ap.WDDIFF(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY), CURDATE())
주의: 함수는 시작일과 종료일을 모두 카운트합니다.
MYSQL 함수는 2일 사이에 영업일을 반환합니다(포함).BETHIN 2 AND 6은 월요일-금요일이며 달력/지역에 따라 조정할 수 있습니다.
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetBusinessDaysBetweenDates`(d1 DATE, d2 DATE) RETURNS int(11)
BEGIN
DECLARE bDaysInPeriod INT;
SET bDaysInPeriod=0;
WHILE d1<=d2 DO
IF DAYOFWEEK(d1) BETWEEN 2 AND 6 THEN
SET bDaysInPeriod=bDaysInPeriod+1;
END IF;
SET d1=d1+INTERVAL 1 day;
END WHILE;
RETURN bDaysInPeriod;
END
헬로 테스트 부탁합니다.
DELIMITER $$
DROP FUNCTION IF EXISTS `WORKDAYS` $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
DECLARE cnt INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
SELECT COUNT(*) INTO cnt FROM `holiday` WHERE (hday BETWEEN start_date AND end_date) and (DAYOFWEEK(hday) != 7 and DAYOFWEEK(hday) != 1);
SET diff = DATEDIFF(end_date, start_date) ;
RETURN (CASE WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Saturday' THEN (diff - cnt)
WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Sunday' THEN (diff - 2 - cnt)
WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Sunday' THEN (diff - 1 - cnt)
WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Saturday' THEN (diff + 1 - cnt)
WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) in ('Saturday', 'Sunday') THEN (diff + 1 - cnt)
WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff -1 - cnt)
WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff + 1 - cnt)
WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) not in ('Saturday', 'Sunday')
&& WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2 - cnt)
ELSE (diff - cnt) END)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END);
END $$
및 테이블 홀리데이
DROP TABLE IF EXISTS `holiday`;
CREATE TABLE `holiday` (
`id` bigint(32) unsigned NOT NULL AUTO_INCREMENT,
`hday` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `holiday` (`id`, `hday`) VALUES
(1, '2012-01-01'),
(2, '2012-05-01'),
(3, '2012-05-08'),
(4, '2012-07-05'),
(5, '2012-07-06'),
(6, '2012-09-28'),
(7, '2012-10-28'),
(8, '2012-11-17'),
(9, '2012-12-24'),
(10, '2012-12-25'),
(11, '2012-12-26');
etc...
예를 들어 2016-04년에는 @Rodger Bagnall이 올린 답변이 제대로 작동하지 않습니다.그것은 그것이 실제임을 하루 적게 보여준다.
쿼리에 의한 계산에 대해 말하는 경우 - 다음을 사용합니다.
set
@S = '2016-04-01',
@E = '2016-04-30';
select
case
when WEEKDAY(@S) < 5 then 5 - WEEKDAY(@S)
else 0
end #startweek
+
case
when WEEKDAY(@E) < 5 then WEEKDAY(@E) + 1
else 5
end #endweek
+
(
DATEDIFF(@E, @S) + 1 # plus 1 day cause params is inside 1 month
- (7 - WEEKDAY(@S)) # minus start week
- (WEEKDAY(@E) + 1) # minus end week
) DIV 7 * 5 #rest part
as work_date_count;
쿼리가 숫자의 출처를 표시하기 위해 최적화되지 않았습니다.
MySQL DB에 저장 프로시저를 추가하여 팀의 총 근무일 수를 세었습니다(WORKDAYS라고 부릅니다).
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)
- (SELECT DISTINCT COUNT(PriKey) FROM holidays WHERE date BETWEEN date1 AND date2)
+ (SELECT DISTINCT COUNT(PriKey) FROM weekenddaysworked WHERE date BETWEEN date1 AND date2)
DB에 두 개의 테이블을 추가했습니다. 휴일 및 주말 작업 모두 두 개의 열(PriKey(int, 11), 데이터(date))로 구성되었습니다.
휴일에는 제가 고려해야 할 휴일을 추가하고 주말 근무일에는 직원들이 주말에 일하는 날짜를 추가했습니다.
그 결과 INT를 가진 함수로 절차를 추가하였습니다. date1과 date2는 DATE로 정의됩니다.
이제 MySQL 함수를 다음과 같이 호출할 수 있습니다.
WORKDAYS(날짜1, date2) - 예를 들어 WORKDAYS('2018-11-01', 2018-12-01')
Thsi는 SQL Server 2005에서 동작합니다.
그것이 너에게 효과가 있을지 모르겠다.
DECLARE @StartDate DATETIME,
@EndDate DATETIME
SELECT @StartDate = '22 Nov 2009',
@EndDate = '28 Nov 2009'
;WITH CTE AS(
SELECT @StartDate DateVal,
DATENAME(dw, @StartDate) DayNameVal
UNION ALL
SELECT DateVal + 1,
DATENAME(dw, DateVal + 1)
FROM CTE
WHERE DateVal < @EndDate
)
SELECT COUNT(1)
FROM (
SELECT *
FROM CTE
WHERE DayNameVal NOT IN ('Sunday','Saturday')
) DayVals
이것이 오래된 내용인 것은 알지만, 제 솔루션이 어떤 사람들에게는 도움이 될 수도 있다고 생각하고 있습니다.이것은 함수가 필요 없는 biz days를 찾기 위해 한 질문입니다.필드에 원하는 이름을 붙일 수 있습니다. 일부러 비워뒀을 뿐입니다.
SELECT
@tmp_s := ept.`date_start`,
@tmp_e := IF(ept.`date_end` IS NULL, NOW(),ept.`date_end`),
@start := IF(DAYOFWEEK(@tmp_s)=1,@tmp_s + INTERVAL 1 DAY,(IF(DAYOFWEEK(@tmp_s)=7,@tmp_s + INTERVAL 2 DAY,@tmp_s)),
@end := IF(DAYOFWEEK(@tmp_e)=1,@tmp_e - INTERVAL 2 DAY,(IF(DAYOFWEEK(@tmp_e)=7,@tmp_e - INTERVAL 1 DAY,@tmp_e)),
@bizdays := CASE
WHEN DATEDIFF(@end,@start)>7 THEN CEIL((DATEDIFF(@end,@start)/7)*5)
WHEN DAYOFWEEK(@end)< DAYOFWEEK(@start) THEN DATEDIFF(@end,@start)-2
ELSE DATEDIFF(@end,@start)
END,
DATE(@start),
DATE(@end),
IF(@bizdays>=10,10,@bizdays)
FROM `employee_points` ept
WHERE ept.`date_start` > '2011-01-01'
위의 NETWORKDAYS() 함수의 경우 시작일로부터 종료일이 7일 이내인 경우와 주말에 걸쳐 있는 경우의 조건을 하나 더 추가해야 합니다.
RETURN (diff + 1)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END)
- (CASE WHEN diff<7 and WEEK(start_date)<>WEEK(end_date) THEN 2 ELSE 0 end);
비록 오래된 포스트지만 많은 도움이 된다.@shahcool에서 제공하는 솔루션이 정확한 일수가 반환되지 않습니다.
Workdays('2013-03-26','2013-04-01')돌아가다3며칠, 그러나 실제로 있어야 한다5날들
아래는 정확한 영업일수를 테스트하고 재실행한 솔루션입니다.
DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYS $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
DECLARE NumberOfWeeks INT;
DECLARE RemainingDays INT;
DECLARE firstDayOfTheWeek INT;
DECLARE lastDayOfTheWeek INT;
DECLARE WorkingDays INT;
IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;
## Add one to include both days in interval
SET diff = DATEDIFF(end_date, start_date)+1;
SET NumberOfWeeks=floor(diff/7);
SET RemainingDays=MOD(diff,7);
SET firstDayOfTheWeek=DAYOFWEEK(start_date);
SET lastDayOfTheWeek=DAYOFWEEK(end_date);
IF(firstDayOfTheWeek <= lastDayOfTheWeek) THEN
IF( firstDayOfTheWeek<=6 AND 6 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF;
IF( firstDayOfTheWeek<=7 AND 7 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF;
ELSE
IF( firstDayOfTheWeek=7) THEN SET RemainingDays=RemainingDays-1;
IF (lastDayOfTheWeek=6) THEN SET RemainingDays=RemainingDays-1; END IF;
ELSE SET RemainingDays=RemainingDays-2;
END IF;
END IF;
SET WorkingDays=NumberOfWeeks*5;
IF(RemainingDays>0) THEN RETURN WorkingDays+RemainingDays;
ELSE RETURN WorkingDays; END IF;
END $$
DELIMITER ;
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
두 가지 기능이 필요했어요.하나는 두 날짜 사이의 영업일 수를 계산하고 하나는 날짜에 x 영업일을 추가/감산합니다.여기 제가 인터넷에서 찾은 예시를 정리한 것이 있습니다.이들은 표준 DATIFF() 함수 및 DATE_ADD() 함수에 근접하여 서로의 계산을 보완합니다.예를 들어 DateDiffBusiness('2014-05-14', DateAddBusiness('2014-05-14',5))는 5와 같습니다.
DROP FUNCTION IF EXISTS DateDiffBusiness;
DELIMITER &
CREATE FUNCTION DateDiffBusiness( d2 DATE, d1 DATE )
RETURNS INT
DETERMINISTIC
COMMENT 'Calculates the number of bussiness days between two dates'
BEGIN
DECLARE dow1, dow2, days INT;
SET dow1 = DAYOFWEEK(d1);
SET dow2 = DAYOFWEEK(d2);
SET days = FLOOR( DATEDIFF(d2,d1)/7 ) * 5 +
CASE
WHEN dow1=1 AND dow2=7 THEN 5
WHEN dow1 IN(7,1) AND dow2 IN (7,1) THEN 0
WHEN dow1=dow2 THEN 1
WHEN dow1 IN(7,1) AND dow2 NOT IN (7,1) THEN dow2-1
WHEN dow1 NOT IN(7,1) AND dow2 IN(7,1) THEN 7-dow1
WHEN dow1<=dow2 THEN dow2-dow1+1
WHEN dow1>dow2 THEN 5-(dow1-dow2-1)
ELSE 0
END;
RETURN days-1;
END&
DELIMITER ;
DROP FUNCTION IF EXISTS DateAddBusiness;
DELIMITER &
CREATE FUNCTION DateAddBusiness(mydate DATE, numday INT)
RETURNS DATE
DETERMINISTIC
COMMENT 'Adds bussiness days between two dates'
BEGIN
DECLARE num_week INT DEFAULT 0;
DECLARE num_day INT DEFAULT 0;
DECLARE adj INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
SET num_week = numday DIV 5;
SET num_day = MOD(numday, 5);
IF (WEEKDAY(mydate) + num_day >= 5) then
SET adj = 2;
END IF;
SET total = num_week * 7 + adj + num_day;
RETURN DATE_ADD(mydate, INTERVAL total DAY);
END&
DELIMITER ;
NETWORKDAYS를 에뮬레이트하는 함수입니다.Rodger Bagnall 솔루션을 기반으로 한 INTL https://stackoverflow.com/a/6762805/218418
DELIMITER //
DROP FUNCTION IF EXISTS NETWORKDAYS//
CREATE FUNCTION NETWORKDAYS(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
RETURN (5 * (DATEDIFF(ed, sd) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1))+1;
END//
DELIMITER ;
그리고 선택하기 위해
SELECT NETWORKDAYS('2015-01-01 06:00:00', '2015-01-20 06:00:00');
이는 +ve와 -ve의 차이에 모두 유효한 DATIFF 대체품입니다.
DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYSDIFF$$
CREATE FUNCTION WORKDAYSDIFF(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
RETURN IF (sd >= ed,
5 * (DATEDIFF(sd, ed) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(ed) + WEEKDAY(sd) + 1, 1),
-(5 * (DATEDIFF(ed, sd) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1)) );
END$$
DELIMITER ;
이 솔루션을 사용하고 있습니다.마지막으로, 다음을 봐 주세요.
DROP FUNCTION IF EXISTS datediff_workdays;
CREATE FUNCTION datediff_workdays(start_date DATE, end_date DATE) RETURNS INTEGER
BEGIN
RETURN 5 * (DATEDIFF(end_date, start_date) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(start_date) + WEEKDAY(end_date) + 1, 1);
END
@caveman, @bryan-geraghty, @rodger-bagnall의 답변을 바탕으로 "weekdays agnall" 쿼리에 대해서도 거꾸로 계산할 수 있는 버전이 필요했습니다.이 적응은 start_date가 end_date 이전 또는 이후일 때 작동합니다.
SELECT 5 * (DATEDIFF(@E, @S) DIV 7) +
CASE WHEN @E < @S THEN
-1 * MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(@E) + WEEKDAY(@S) + 1, 1)
ELSE
MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
END
두 사례의 샘플 결과:
+------------+------------+-----------+
| @S | @E | wday_diff |
+------------+------------+-----------+
| 2019-11-25 | 2019-10-26 | -20 |
| 2019-11-25 | 2019-11-28 | 3 |
+------------+------------+-----------+
버그가 발견되면 알려주세요.
언급URL : https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates
'programing' 카테고리의 다른 글
| MySQL이 여러 문자를 대체할 수 있습니까? (0) | 2022.11.05 |
|---|---|
| 케이스/스위치 스테이트먼트에 대응하는 Python은 무엇입니까? (0) | 2022.11.05 |
| mysqldump 출력을 작은 파일로 분할하려면 어떻게 해야 합니까? (0) | 2022.11.05 |
| 객체의 키를 찾으려면 어떻게 해야 합니까? (0) | 2022.11.05 |
| 도커 컴포지트를 사용하여 도커라이즈된 스프링 부트 및 MariaDb를 시작할 때 Connection Rejected 오류가 발생함 (0) | 2022.11.05 |