Generating Weekdays Data in MySQL: A Custom Stored Procedure

What Is the First Day of the Week?

Sunday is the first day of the week for roughly half the world. Monday is the first day of the week for the other half.

Problem Statement:

When dealing with date and time data, it can be useful to generate weekday data for a specific time range. While there are built-in MySQL functions for this purpose, they may not always meet your specific requirements.

We require a solution that can generate weekdays data containing week number, year, week start, and week end. This data can be utilized to support any of our data analysis operations.

Solution:

The InsertWeekDays stored procedure provides a solution to this problem by allowing developers to generate a table of weekdays based on any start day of the week.

By passing in parameters for the start year, end year, and start day of the week, the stored procedure will dynamically generate a table of weekdays that can be used in other parts of the application or system.

This flexibility ensures that developers can work with date and time data in a way that accurately reflects the start day of the week used in their region or industry.

DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `InsertWeekDays`(IN start_year INT, IN end_year INT ,IN week_start_day VARCHAR(10))
BEGIN
    DROP TABLE IF EXISTS weekdays;
    CREATE TABLE weekdays (weeknumber INT, years INT, weekstart DATE, weekend DATE);

    SET @start_date := CONCAT(start_year, '-01-01');
    SET @end_date := CONCAT(end_year, '-12-31');

    WHILE @start_date <= @end_date DO
        SET @weekday_offset := CASE week_start_day
            WHEN 'MONDAY' THEN 0
            WHEN 'TUESDAY' THEN 1
            WHEN 'WEDNESDAY' THEN 2
            WHEN 'THURSDAY' THEN 3
            WHEN 'FRIDAY' THEN 4
            WHEN 'SATURDAY' THEN 5
            WHEN 'SUNDAY' THEN 6
        END;

        SET @week_mode := CASE 
            WHEN @weekday_offset = 0 THEN 7 -- If start day is Monday, set mode to 7 
            ELSE 2 -- Otherwise, use default mode 2 as the 55% population in the world start day is Sunday
        END;

        INSERT INTO weekdays
             SELECT WEEK(@start_date, @week_mode), 
             YEAR(@start_date),
                   DATE_SUB(@start_date, INTERVAL (WEEKDAY(@start_date) + 7 - @weekday_offset) % 7 DAY),
                   DATE_ADD(DATE_SUB(@start_date, INTERVAL (WEEKDAY(@start_date) + 7 - @weekday_offset) % 7 DAY), INTERVAL 6 DAY)
            FROM (SELECT 1) AS dummy
            WHERE YEAR(@start_date + INTERVAL 6 DAY) <= end_year;
        SET @start_date := DATE_ADD(@start_date, INTERVAL 7 DAY);
    END WHILE;
END //
DELIMITER ;

Run Store Procedure:

• 1st Parameter : specifies the start year for generating the data.

• 2nd Parameter : specifies the end year for generating the data.

• 3rd Parameter : allows you to specify the starting day of the week depending on your country or region's customs.

-- CALL InsertWeekDays(start_year, end_year,week_start_day);
CALL InsertWeekDays(2023, 2025,'MONDAY');

Verification:

Benefits:

The InsertWeekDays stored procedure offers several benefits to developers, including:

• Dynamic generation of weekdays based on the start day of the week: Developers can easily generate a table of weekdays based on any start day of the week, ensuring that date and time data are organized in a way that accurately reflects their needs.

• Simplified data management: By generating a table of weekdays in MySQL, developers can simplify the management of date and time data within their applications and systems.

• Improved accuracy and precision: The ability to generate weekdays based on any start day of the week ensures that date and time data is accurate and precise, reflecting the needs of specific regions or industries.

Limitations:

It should be noted that the InsertWeekDays stored procedure has a limitation in that, the standard MySQL WEEK() function can only generate week numbers based on either Sunday or Monday as the start day of the week.

If a different start day of the week is used, the stored procedure will default to Sunday (as 55% world's population has Sunday as the start day of the week) mode 2 of the WEEK()function to generate the week number.

It is important to note that this limitation only impacts the calculation of the week number using the WEEK()function. The week start and week end dates are not impacted by this limitation and work properly.

However, this limitation does not affect the ability of the stored procedure to generate weekdays based on any start day of the week.

Conclusion:

The InsertWeekDays stored procedure offers a flexible and powerful solution to the challenge of generating weekdays in MySQL based on any start day of the week.

With its ability to dynamically generate a table of weekdays based on start year, end year, and start day of the week, this stored procedure can help developers to manage and manipulate date and time data in a way that is accurate, precise, and customized to their needs.

References :

https://www.timeanddate.com/calendar/days/first-day-of-the-week.html

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week

Gist:

https://gist.github.com/raowaqasakram/3c3c55a15d8c4fd6acbba2f9d74df6f5

Happy Learning !