Skip to content

Instantly share code, notes, and snippets.

@jenyayel

jenyayel/working_days.sql

Last active April 7, 2022 08:01
Show Gist options
  • Save jenyayel/cfb6d6542547c6ee1a1776eceeb1e9bd to your computer and use it in GitHub Desktop.
Save jenyayel/cfb6d6542547c6ee1a1776eceeb1e9bd to your computer and use it in GitHub Desktop.
Download ZIP
Get next working day in MySQL
Raw
working_days.sql
/*
* Returns calendar dates along with their next working date for the last 2 years.
* Non working days are either weekends or "hardcoded" holidays.
* Author: Jenya Y.
*/
SELECT
CAST(calendar_date AS DATE) AS calendar_date,
MIN(working_days.working_date) AS next_working_day
FROM
(
VALUES
(
SEQUENCE(
date_add('day', - 365*2, current_date),
current_date,
INTERVAL '1' DAY
)
)
) AS t1(date_array)
CROSS JOIN UNNEST(date_array) AS t2(calendar_date)
LEFT JOIN (
SELECT
CAST(working_date AS DATE) AS working_date
FROM
(
VALUES
(
SEQUENCE(
date_add('day', - 365*2, current_date),
current_date,
INTERVAL '1' DAY
)
)
) AS t1(date_array)
CROSS JOIN UNNEST(date_array) AS t2(working_date)
WHERE
day_of_week(working_date) NOT IN (6, 7)
AND working_date NOT IN (
from_iso8601_date('2017-01-02') /* New Year's Day */
, from_iso8601_date('2017-01-16') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2017-01-20') /* Inauguration Day */
, from_iso8601_date('2017-02-20') /* Washington's Birthday */
, from_iso8601_date('2017-04-14') /* Good Friday */
, from_iso8601_date('2017-04-14') /* Good Friday */
, from_iso8601_date('2017-05-29') /* Memorial Day */
, from_iso8601_date('2017-07-04') /* Independence Day */
, from_iso8601_date('2017-09-04') /* Labour Day */
, from_iso8601_date('2017-10-09') /* Columbus Day */
, from_iso8601_date('2017-11-10') /* Veterans Day */
, from_iso8601_date('2017-11-23') /* Thanksgiving Day */
, from_iso8601_date('2017-12-25') /* Christmas Day */
, from_iso8601_date('2018-01-01') /* New Year's Day */
, from_iso8601_date('2018-01-15') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2018-02-19') /* Washington's Birthday */
, from_iso8601_date('2018-03-30') /* Good Friday */
, from_iso8601_date('2018-03-30') /* Good Friday */
, from_iso8601_date('2018-05-28') /* Memorial Day */
, from_iso8601_date('2018-07-04') /* Independence Day */
, from_iso8601_date('2018-09-03') /* Labour Day */
, from_iso8601_date('2018-10-08') /* Columbus Day */
, from_iso8601_date('2018-11-12') /* Veterans Day */
, from_iso8601_date('2018-11-22') /* Thanksgiving Day */
, from_iso8601_date('2018-12-25') /* Christmas Day */
, from_iso8601_date('2019-01-01') /* New Year's Day */
, from_iso8601_date('2019-01-21') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2019-02-18') /* Washington's Birthday */
, from_iso8601_date('2019-04-19') /* Good Friday */
, from_iso8601_date('2019-04-19') /* Good Friday */
, from_iso8601_date('2019-05-27') /* Memorial Day */
, from_iso8601_date('2019-07-04') /* Independence Day */
, from_iso8601_date('2019-09-02') /* Labour Day */
, from_iso8601_date('2019-10-14') /* Columbus Day */
, from_iso8601_date('2019-11-11') /* Veterans Day */
, from_iso8601_date('2019-11-28') /* Thanksgiving Day */
, from_iso8601_date('2019-12-25') /* Christmas Day */
, from_iso8601_date('2020-01-01') /* New Year's Day */
, from_iso8601_date('2020-01-20') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2020-02-17') /* Washington's Birthday */
, from_iso8601_date('2020-04-10') /* Good Friday */
, from_iso8601_date('2020-04-10') /* Good Friday */
, from_iso8601_date('2020-05-25') /* Memorial Day */
, from_iso8601_date('2020-07-03') /* Independence Day */
, from_iso8601_date('2020-09-07') /* Labour Day */
, from_iso8601_date('2020-10-12') /* Columbus Day */
, from_iso8601_date('2020-11-11') /* Veterans Day */
, from_iso8601_date('2020-11-26') /* Thanksgiving Day */
, from_iso8601_date('2020-12-25') /* Christmas Day */
, from_iso8601_date('2021-01-01') /* New Year's Day */
, from_iso8601_date('2021-01-18') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2021-01-20') /* Inauguration Day */
, from_iso8601_date('2021-02-15') /* Washington's Birthday */
, from_iso8601_date('2021-04-02') /* Good Friday */
, from_iso8601_date('2021-04-02') /* Good Friday */
, from_iso8601_date('2021-05-31') /* Memorial Day */
, from_iso8601_date('2021-06-18') /* Juneteenth */
, from_iso8601_date('2021-07-05') /* Independence Day */
, from_iso8601_date('2021-09-06') /* Labour Day */
, from_iso8601_date('2021-10-11') /* Columbus Day */
, from_iso8601_date('2021-11-11') /* Veterans Day */
, from_iso8601_date('2021-11-25') /* Thanksgiving Day */
, from_iso8601_date('2021-12-24') /* Christmas Day */
, from_iso8601_date('2021-12-31') /* New Year's Day */
, from_iso8601_date('2022-01-17') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2022-02-21') /* Washington's Birthday */
, from_iso8601_date('2022-04-15') /* Good Friday */
, from_iso8601_date('2022-04-15') /* Good Friday */
, from_iso8601_date('2022-05-30') /* Memorial Day */
, from_iso8601_date('2022-06-20') /* Juneteenth */
, from_iso8601_date('2022-07-04') /* Independence Day */
, from_iso8601_date('2022-09-05') /* Labour Day */
, from_iso8601_date('2022-10-10') /* Columbus Day */
, from_iso8601_date('2022-11-11') /* Veterans Day */
, from_iso8601_date('2022-11-24') /* Thanksgiving Day */
, from_iso8601_date('2022-12-26') /* Christmas Day */
, from_iso8601_date('2023-01-02') /* New Year's Day */
, from_iso8601_date('2023-01-16') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2023-02-20') /* Washington's Birthday */
, from_iso8601_date('2023-04-07') /* Good Friday */
, from_iso8601_date('2023-04-07') /* Good Friday */
, from_iso8601_date('2023-05-29') /* Memorial Day */
, from_iso8601_date('2023-06-19') /* Juneteenth */
, from_iso8601_date('2023-07-04') /* Independence Day */
, from_iso8601_date('2023-09-04') /* Labour Day */
, from_iso8601_date('2023-10-09') /* Columbus Day */
, from_iso8601_date('2023-11-10') /* Veterans Day */
, from_iso8601_date('2023-11-23') /* Thanksgiving Day */
, from_iso8601_date('2023-12-25') /* Christmas Day */
, from_iso8601_date('2024-01-01') /* New Year's Day */
, from_iso8601_date('2024-01-15') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2024-02-19') /* Washington's Birthday */
, from_iso8601_date('2024-03-29') /* Good Friday */
, from_iso8601_date('2024-03-29') /* Good Friday */
, from_iso8601_date('2024-05-27') /* Memorial Day */
, from_iso8601_date('2024-06-19') /* Juneteenth */
, from_iso8601_date('2024-07-04') /* Independence Day */
, from_iso8601_date('2024-09-02') /* Labour Day */
, from_iso8601_date('2024-10-14') /* Columbus Day */
, from_iso8601_date('2024-11-11') /* Veterans Day */
, from_iso8601_date('2024-11-28') /* Thanksgiving Day */
, from_iso8601_date('2024-12-25') /* Christmas Day */
, from_iso8601_date('2025-01-01') /* New Year's Day */
, from_iso8601_date('2025-01-20') /* Martin Luther King, Jr. Day */
, from_iso8601_date('2025-01-20') /* Inauguration Day */
, from_iso8601_date('2025-02-17') /* Washington's Birthday */
, from_iso8601_date('2025-04-18') /* Good Friday */
, from_iso8601_date('2025-04-18') /* Good Friday */
, from_iso8601_date('2025-05-26') /* Memorial Day */
, from_iso8601_date('2025-06-19') /* Juneteenth */
, from_iso8601_date('2025-07-04') /* Independence Day */
, from_iso8601_date('2025-09-01') /* Labour Day */
, from_iso8601_date('2025-10-13') /* Columbus Day */
, from_iso8601_date('2025-11-11') /* Veterans Day */
, from_iso8601_date('2025-11-27') /* Thanksgiving Day */
, from_iso8601_date('2025-12-25') /* Christmas Day */
)
) AS working_days ON working_days.working_date >= date_add('day', + 1, calendar_date)
GROUP BY
calendar_date
ORDER BY
calendar_date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment