Excel имеет функцию NETWORKDAYS(), которая находит количество рабочих дней между двумя датами.
У кого-нибудь есть аналогичная функция для MySQL? Поскольку праздники добавляют сложность, решение не должно иметь дело с праздниками.
Функция MySQL позволяет найти количество рабочих дней между двумя датами
Ответ 1
Это выражение -
5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
вычисляет количество рабочих дней между датой начала @S и датой окончания @E.
Предполагает, что дата окончания (@E) не до даты начала (@S). Совместимость с DATEDIFF в том, что та же самая дата начала и дата окончания дает нулевые рабочие дни. Игнорирует праздники.
Строка цифр строится следующим образом. Создать таблицу стартовые дни и дни окончания, строки должны начинаться с понедельника (WEEKDAY 0), а столбцы должны начинаться с понедельника. Заполните диагональ сверху слева направо со всеми 0 (то есть 0 рабочие дни с понедельника по понедельник, вторник и вторник и т.д.). Для каждого дня начинайте с диагонали (всегда должно быть 0) и заполните столбцы справа, один день за раз. Если вы приземлитесь на день недели (не рабочий день), количество рабочих дней не меняется, он переносится слева. В противном случае число рабочих дней увеличивается на единицу. Когда вы достигнете конца строка цикла возвращается к началу той же строки и продолжается до тех пор, пока вы снова достигните диагонали. Затем перейдите к следующей строке.
например. Предполагая, что суббота и воскресенье не являются рабочими днями -
| 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 значений в таблице в строку.
Пожалуйста, дайте мне знать, если вы найдете какие-либо ошибки.
-Edit улучшенная таблица:
| 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
улучшенная строка: '0123444401233334012222340111123400001234000123440'
улучшенное выражение:
5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
Ответ 2
Поскольку вам нужно будет отслеживать праздники где-нибудь, таблица календаря кажется подходящей:
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)
)
Конечно, вам нужно заполнить его всеми датами за любой период времени, который вы когда-либо могли бы использовать в своем приложении. Поскольку существует только 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.
Ответ 3
Это решение использует в основном тот же подход, что и Rodger, за исключением того, что метод генерации матрицы намного сложнее. Примечание. Этот выход этого решения несовместим с NETWORKDAYS.
Как и в решении Роджера, это вычисляет количество рабочих дней между датой начала (@S) и датой окончания (@E) без необходимости определять хранимую процедуру. Предполагается, что дата окончания не до даты начала. Использование одной и той же даты начала и окончания будет производить 0. Праздники не принимаются во внимание.
Основное различие между этим и решением Роджера заключается в том, что матрица и результирующая строка цифр построены сложным алгоритмом, который я не включил. Выход этого алгоритма проверяется на unit test (см. Тестовые входы и выходы ниже). В матрице пересечение любой пары значений x и y (WEEKDAY (@S) и WEEKDAY (@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
Ответ 4
Просто для дальнейших ссылок. Ни одно из вышеперечисленных не работало для меня, а модифицированная версия @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)
Ответ 5
Могут ли предлагаемые строки быть неправильными?
DATEDIFF (from, to) исключает 'to'. Точно так же должна быть эта строка:
Понедельник → пятница = {Пн, Вт, Ср, Т) = 4
Понедельник → Суббота = {Пн, Вт, Ср, Чт, Пт} = 5
Вторник → Понедельник = {tу, ср, Чт, Пт, пропустить Сб, пропустить вс, пн исключен} = 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'
Мне что-то здесь не хватает?:)
Ответ 6
Учитывая первый день месяца, это вернет число будних дней в течение этого месяца. В 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)
Ответ 7
Основываясь на вышеприведенной функции Yada, здесь приводится небольшая вариация на тему, которая рассчитывает рабочие дни, оставшиеся с текущей даты (не включая), до указанной даты. Он также обрабатывает различные выходные в Израиле:-) Обратите внимание, что это приведет к отрицательному результату, если целевая дата в прошлом (это именно то, что я хотел).
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 ;
Ответ 8
Решение Yada работает неправильно. Мои изменения:
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 ;
Ответ 9
Проблема, с которой вы столкнетесь с параметром "игнорирование праздника", - это каждая страна будет иметь разные праздники.
Вам нужно будет начать с определения праздников для своей страны, а затем пройти через них, чтобы узнать, является ли определенная дата праздником.
Я не знаю общих функций, которые делают то, что вы хотите в mysql
К сожалению!
Ответ 10
Разница между днями недели может быть достигнута следующим образом:
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())
Примечание. Функция подсчитывает дату начала и окончания
Ответ 11
Ответа на этот вопрос @Rodger Bagnall не работает правильно для меня, например, 2016-04. Он показывает на 1 день меньше, чем в реальном.
если говорить о вычислении по запросу - я использую это:
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;
Запрос не оптимизирован, чтобы показать, где числа поступают из
Ответ 12
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
Ответ 13
Я знаю, что это старый поток, но думал, что мое решение может быть полезно для некоторых людей. это запрос, который я сделал, чтобы найти дни biz без необходимости в функциях. вы можете назвать поля, что хотите, я просто оставил их пустым.
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'
Ответ 14
Для вышеописанной функции 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);
Ответ 15
Хотя очень OLD Post, но помогает много. В соответствии с решением, предоставляемым @shahcool, не возвращается Точные дни например.
Workdays('2013-03-26','2013-04-01') Возврат 3 Дни, но на самом деле там должно быть 5 Days
Ниже приведено решение, которое я проверил и проверил точные рабочие дни
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 ;
Ответ 16
Функция MYSQL возвращает рабочие дни между двумя датами (включительно). МЕЖДУ 2 И 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
Ответ 17
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
Ответ 18
Мне нужны были две функции. Один, чтобы рассчитать количество рабочих дней между двумя датами и один, чтобы добавить/вычесть x рабочих дней на дату. Вот что я собрал из примеров, которые я нашел в Интернете. Они сделаны близкими к стандартным функциям DATEDIFF() и 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 ;
Ответ 19
Helooo проверьте пожалуйста.
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...
Ответ 20
Функция, которая эмулирует NETWORKDAYS.INTL на основе решения Rodger Bagnall fooobar.com/questions/183960/...
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');
Ответ 21
Это замена для DATEDIFF, которая работает для различий + ve и -ve.
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 ;
Ответ 22
Этот запрос легко возвращает количество рабочих дней между двумя датами, исключая выходные дни:
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));
Ответ 23
У меня было это требование и я написал полную функцию, которая может вычисляться, избегая часов выходных и праздничных дней для данной страны (используя отдельную таблицу). Я поместил всю функцию и детали в свой блог (http://mgw.dumatics.com/mysql-function-to-calculate-elapsed-working-time/), а также объяснение и блок-схему и создание праздничной таблицы и т.д.... я с удовольствием поместил бы его здесь, но это слишком долго....
Пример разрешенной проблемы:
Скажем, инцидент был зарегистрирован в пятницу 10 июня 2016 года в 12:00 для сайта в "Великобритании", который открывается с 09:00 до 16:00. Этот инцидент был затем закрыт во вторник 14 июня 2016 года в 14:00.
Для вышеупомянутой функции инцидента должен быть рассчитан возраст как 960 минут = 16 часов = [4 часа в пятницу (с 12:00 до 16:00) + 7 часов в понедельник (с 09:00 до 16:00) + 5 часов во вторник (с 09:00 до 14:00)]
Ответ 24
Если вы хотите по-настоящему проигнорировать существование выходных дней, тогда вам нужно обработать что-то, что происходит на Sat/Sun, как если бы оно возникло в Mon; и что-то, что заканчивается на Sat/Sun, как будто это действительно закончилось на Пт. Поэтому, что начинается и заканчивается в выходные, вы должны игнорировать как начало, так и конец. Я не думаю, что любой из других ответов сделал это.
Следующая функция выполняет следующие действия:
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;
На языке ответа Роджера таблица, которая создала строку выше, ниже (единственная разница, если она равна -1 вместо 0 для начала и окончания в субботу/воскресенье):
| 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
Ответ 25
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 и аналогичным образом.
Я вычислил строку, используя логику, упомянутую в самом голосованном ответе, и получил результаты по мере необходимости.
Ответ 26
Я использую это решение, наконец, посмотрю:
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
Ответ 27
Верхний ответ подсчитывается для дней между начальной и конечной датой, но исключает конечную дату.
Также для любых дат, которые начинаются и заканчиваются в одни и те же выходные дни, скажем, с субботы 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$$
Ответ 28
Я добавил хранимую процедуру в мою базу данных MySQL, чтобы подсчитать общее количество рабочих дней моей команды (я назвал ее 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)
Я добавил две таблицы в свою базу данных: праздничные и выходные дни с двумя столбцами (PriKey (int, 11), data (date))
В праздники я добавил праздники, которые мне нужно было принять во внимание, а в выходные дни работал, я добавил даты, когда мои ребята работали на выходных.
Я добавил процедуру как функцию с INT в качестве результата. date1 и date2 определены как DATE.
Теперь я могу вызвать функцию MySQL так:
РАБОЧИЕ ДНИ (дата1, дата2) - например, РАБОЧИЕ ДНИ ('2018-11-01', '2018-12-01')
Ответ 29
Хорошо, мальчики и девочки, у меня, очевидно, лучшее решение, вот простое утверждение о выборе числа дней недели между двумя датами.
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 выходных дня
Ответ 30
Вам нужно будет использовать DATEDIFF, чтобы получить количество дней между двумя датами в MySQL. IE:
DATEDIFF(t.date_column_1, t.date_column_2)
Но Стефан в противном случае прав - праздники федеральные и региональные. Вам нужно создать таблицу для хранения дат и указать их в своих расчетах.