mysql количество рабочих дней

Функция MySQL для поиска количества рабочих дней между двумя датами

Excel имеет функцию NETWORKDAYS (), которая находит количество рабочих дней между двумя датами.

У кого-нибудь есть аналогичная функция для MySQL? Поскольку праздники добавляют сложности, решение не должно иметь дело с праздниками.

29 ответов

вычисляет количество рабочих дней между датой начала @S и датой окончания @E.

предполагается, что дата окончания (@E) не предшествует дате начала (@S). Совместимость с DATEDIFF в том же дата начала и дата окончания дает ноль рабочих дней. Игнорирует праздники.

затем объедините 49 значений в таблице в строку.

пожалуйста, дайте мне знать, если вы обнаружите какие-либо ошибки.

-редактировать улучшенная таблица:

улучшена строка: ‘0123444401233334012222340111123400001234000123440’

поскольку вам нужно будет отслеживать праздники где-то, таблица календаря кажется подходящей:

вам, конечно, нужно заполнить его всеми датами за любой период времени, с которым вы можете работать в своем приложении. Поскольку в году всего 365 (или 366) дней, переход с 1900 на 2100 не имеет большого значения. Просто убедитесь, что вы загружаете его со всеми датами, а не только праздники.

в этот момент запросы, как тот, который вам нужно стать тривиально:

предостережение: я работаю в основном с MS SQL и не работал с MySQL в течение длительного времени, поэтому вам может потребоваться настроить выше. Например, я даже не помню, имеет ли MySQL битный тип данных.

это решение использует в основном тот же подход, что и у Роджера, за исключением того, что метод генерации матрицы намного сложнее. Примечание: этот выход такого решения не совместимые с ЧИСТРАБДНИ.

как и в решении Роджера, это вычисляет количество рабочих дней между датой начала (@S) и датой окончания (@E) без необходимости определения хранимой процедуры. Предполагается, что дата окончания не ранее даты начала. Использование одной и той же даты начала и окончания приведет к 0. Праздники не учитываются.

основное различие между этим и решением Роджера заключается в том, что матрица и результирующая строка цифр построены сложным алгоритмом, который я не включил. Выход этого алгоритма проверяется модульным тестом (см. входы и выходы теста ниже). В матрице пересечение любой заданной пары значений x и y (WEEKDAY(@S) и WEEKDAY(@E)) дает разницу в рабочих днях между двумя значениями. Порядок назначения на самом деле неважно, как эти два сложены вместе, чтобы построить позицию.

рабочие дни понедельник-пятница

49 значений в таблице объединены в следующую строку:

В конце концов, правильное выражение:

Я проверил следующие входы и выходы, используя это решение:

просто для дальнейшего использования. Ничто из вышеперечисленного не работало для меня, но модифицированная версия @Jeff Kooser:

предлагаемый строк быть не так?

DATEDIFF (from, to) исключает ‘to’. Точно так же и эта строка:

Источник

6 запросов для MySQL

Искусство в построении запросов с использованием Structured Query Language (язык структурированных запросов) заключается в создании правильных, эффективных вопросов и команд для базы данных. В запросах SELECT можно использовать ключевые слова JOIN, WHERE и HAVING для уточнения результатов, GROUP BY для комбинирования результатов в легко анализируемые блоки, и UNION для комбинирования результатов нескольких запросов. Команды INSERT, DELETE и UPDATE могут использовать JOIN. Запрос INSERT … SELECT вставляет результаты в другую таблицу. В запросах DELETE и UPDATE можно использовать ключевое слово WHERE, чтобы указать область действия.

Ниже приводятся 6 запросов для MySQL.

1. Возраст в годах

2. Разница между двумя датами

Находим разницу между двумя датами, выраженную в секундах, минутах, часах или днях. Если dt1 и dt2 значения дат в формате ‘yyyy-mm-dd hh:mm:ss’, то количество секунд между dt1 и dt2 вычисляется:

3. Выводим значение, которое встречается в столбце N раз

4. Вычисляем количество рабочих дней между двумя датами

Простейшим способом вычислить количество рабочих дней между двумя датами является использование таблицы календаря calendar со столбцами даты d и логического флага праздников holiday для всех дней во всех возможных годах. Затем используется соответствующий запрос, который возвращает количество рабочих дней между двумя датами Start и Stop включительно:

5. Находим основной ключ для таблицы

6. Определяем, насколько велика таблица

Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: www.codeforest.net/6-useful-mysql-queries
Перевел: Сергей Фастунов
Урок создан: 15 Марта 2011
Просмотров: 39309
Правила перепечатки

5 последних уроков рубрики «Разное»

Как выбрать хороший хостинг для своего сайта?

Выбрать хороший хостинг для своего сайта достаточно сложная задача. Особенно сейчас, когда на рынке услуг хостинга действует несколько сотен игроков с очень привлекательными предложениями. Хорошим вариантом является лидер рейтинга Хостинг Ниндзя — Макхост.

Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

Разработка веб-сайтов с помощью онлайн платформы Wrike

20 ресурсов для прототипирования

Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.

Топ 10 бесплатных хостингов

Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.

Источник

Функция MySQL для определения количества рабочих дней между двумя датами

В Excel есть функция NETWORKDAYS(), которая определяет количество рабочих дней между двумя датами.

У кого-нибудь есть аналогичная функция для MySQL? Поскольку праздники добавляют сложность, решение не должно иметь дело с праздниками.

36 ответов

вычисляет количество рабочих дней между начальной датой @S и конечной датой @E.

Предполагается, что дата окончания (@E) не раньше даты начала (@S). Совместим с DATEDIFF тем, что одна и та же дата начала и дата окончания дают ноль рабочих дней. Игнорирует праздники.

Строка цифр строится следующим образом. Создайте таблицу начальных и конечных дней, строки должны начинаться с понедельника (WEEKDAY 0), а столбцы также должны начинаться с понедельника. Заполните диагональ сверху вниз слева направо всеми 0 (т. Е. Есть 0 рабочих дней между понедельником и понедельником, вторником и вторником и т. Д.). Для каждого дня начните с диагонали (всегда должно быть 0) и заполните столбцы справа, один день за раз. Если вы попадаете в столбец выходных (нерабочий день), число рабочих дней не меняется, оно переносится слева. В противном случае количество рабочих дней увеличивается на один. Когда вы дойдете до конца строки, вернитесь к началу той же строки и продолжайте, пока не дойдете до диагонали. Затем перейдите к следующему ряду.

Затем объедините 49 значений в таблице в строку.

Пожалуйста, дайте мне знать, если вы найдете какие-либо ошибки.

Редактировать улучшенную таблицу:

улучшенная строка: ‘0123444401233334012222340111123400001234000123440’

Это решение использует в основном тот же подход, что и метод Роджера, за исключением того, что метод генерации матрицы намного сложнее. Примечание. Этот вывод этого решения не совместим с NETWORKDAYS.

Как и в решении Роджера, при этом вычисляется количество рабочих дней между начальной датой (@S) и конечной датой (@E) без необходимости определения хранимой процедуры. Предполагается, что дата окончания не раньше даты начала. При использовании одной и той же даты начала и окончания будет получено 0. Отпуск не учитывается.

Основное различие между этим и решением Роджера состоит в том, что матрица и получающаяся строка цифр построены по сложному алгоритму, который я не включил. Выходные данные этого алгоритма проверяются модульным тестом (см. Тестовые входы и выходы ниже). В матрице пересечение любой заданной пары значений x и y (WEEKDAY(@S) и WEEKDAY(@E) дает разницу в рабочих днях между двумя значениями. Порядок присваивания фактически не важен, так как эти два добавляются вместе в подготовить положение.

Рабочие дни с понедельника по пятницу

49 значений в таблице объединены в следующую строку:

В конце правильное выражение:

Я проверил следующие входы и выходы, используя это решение:

Могут ли предложенные строки быть неправильными?

DATEDIFF (от, до) исключает «до». Точно так же и эта строка:

Я что-то здесь упускаю?:)

Поскольку вам нужно будет где-то отслеживать праздники, таблица Календаря кажется подходящей:

Вам, конечно, нужно заполнить все даты за любой период времени, с которым вы можете когда-либо работать в своем приложении. Поскольку в году всего 365 (или 366) дней, переход с 1900 до 2100 не имеет большого значения. Просто убедитесь, что вы загружаете все даты, а не только праздники.

В этот момент запросы, подобные тому, который вам нужен, становятся тривиальными:

Предостережение: я работаю в основном с MS SQL и долгое время не работал с MySQL, поэтому вам, возможно, придется изменить вышеизложенное. Например, я даже не помню, имеет ли MySQL тип данных BIT.

Источник

SQL: решение задачи о рабочем времени

Здравствуйте, в эфире опять Радио SQL! Сегодня у нас решение задачи, которую мы передавали в нашем предыдущем эфире, и обещали разобрать в следующий раз. И вот этот следующий раз наступил.

Задача вызвала живой отклик у гуманоидов галактики Млечный путь (и неудивительно, с их-то трудовым рабством, которое они до сих пор почитают за благо цивилизации). К сожалению, на третьей планете отложили запуск космической обсерватории «Спектр-РГ» в конце июля 2019 года РХ (летоисчисление местное), с помощью которого планировалось транслировать эту передачу. Пришлось искать альтернативные пути передачи, что привело к небольшому опозданию сигнала. Но всё хорошо, что хорошо кончается.

Сразу скажу, что в разборе задачи не будет никакой магии, не надо искать тут откровений или ждать какой-то особо эффективной (или особо какой-нибудь в любом другом смысле) реализации. Это просто разбор задачи. В нём те, кто не знает, как подступаться к решению таких задач, смогут посмотреть, как же их решать. Тем более, что ничего страшного тут нет.

Есть несколько временных интервалов, заданных датой-временем своего начала и конца (пример в синтаксисе PostgreSQL):

Требуется в один SQL-запрос (ц) вычислить продолжительность каждого интервала в рабочих часах. Считаем, что рабочими у нас являются будние дни с понедельника по пятницу, рабочее время всегда с 10:00 до 19:00. Кроме того, в соответствии с производственным календарём РФ существует некоторое количество официальных праздничных дней, которые рабочими не являются, а какие-то из выходных дней, наоборот, являются рабочими из-за переноса тех самых праздников. Укороченность предпраздничных дней учитывать не надо, считаем их полными. Так как праздничные дни год от года меняются, то есть задаются явным перечислением, то ограничимся датами только из 2018 и 2019 годов. Уверен, что при необходимости решение можно будет легко дополнить.

Надо к исходным периодам из periods добавить один столбец с продолжительностью в рабочих часах. Вот какой должен получиться результат:

Исходные данные на корректность не проверяем, считаем всегда start_time

Источник

Календарные функции в MySQL и MariaDB

Как показывает практика, множество систем с использованием календарей обычно строится в виде статических таблиц, в которых перечислены даты и их соответствие рабочим, выходным, праздничным дням. Обычно проблемы начинаются когда система работает без вмешательств программистов достаточно долго и заполненный календарь просто кончается. Для очередного проекта я решил немного оптимизировать данную ситуацию и написал календарь, который создаётся или пересчитывается автоматически, например по встроенному таймеру.

Не знаю, многие ли владеют информацией о наличии в MariaDB(MySQL) встроенного итератора возвращающего значения по заданному диапазону. Если кто не знает, то можно прочитать по ссылке.

Принцип простой. Такой же как у обычного цикла For, которому передаются начало, конец и шаг. Данный итератор имеет фантастическое быстродействие и позволяет производить вычисления последовательностей.

Как все мы знаем, обычный календарь состоит из рабочих и выходных дней, а также из официальных государственных праздничных дней и дней на которые данные праздники будут перенесены, если они выпали на выходные. Перенос праздничных дней обычно происходит по постановлению правительства РФ в середине текущего года.

Создадим 2 таблицы. Периодические ежегодные праздники и перенос выходных дней.

Зальём в таблицы периодические праздники

Обратите внимание, что в таблице holidays присутствует поле hdate_workday, используемое если перенос осуществлён с выходного дня, который переназначен рабочим. Например 20 февраля 2016 года приходится на субботу, но согласно положению правительства, данный день переносится на 22 февраля и обозначен как рабочий. Т.е. появляется рабочая суббота, после которой идёт 3 дня отдыха.

Начальные данные у нас есть, теперь магия SEQUENCE. Создадим таблицу календаря

Для заполнения таблицы создадим хранимую процедуру

Далее достаточно сделать простой запрос

Создание календаря по времени занимает просто копейки.

Но какой же календарь без функций приведения дат. Обычные операции в календаре подразумевают определение дат следующих за искомой через определенный интервал времени. Например предыдущий рабочий день, первый день квартала, последний рабочий день месяца и т.д. Ниже я собрал в единое целое свои наработки и Best Practices из сети по обработке дат

Собственно для чего это нужно? Это нужно для вычисления различной периодики в событиях. Например нужно понять на какое число приходится 6й рабочий день от начала месяца в январе 2017 года.

Или каким числом был понедельник для даты 15 декабря 2016 года.

И т.д. Соответственно функция легко вызывается из обычных запросов и является довольно гибкой. Формат запроса следующий:

где
DATETIME — дата или дата со временем

Если Offset указать отрицательным числом, то вычисление будет производиться назад.

В функции приведения дат, вычисление последних рабочих дней недели, месяца, года сделано независимо от календаря, т.е. для глобального календаря. Если нужна привязка к локальному календарю с праздничными днями, то лучше использовать ‘workday’ offset с отрицательным значением.

Надеюсь кому-то данный материал поможет в разработке.

Источник