CODEWITHSUNDEEP
mysqlsqlsql-insertinsert-into
Armin Orlik
Armin Orlik

Reputation: 195

sql insert data only in working days

I have very simple table which construction looks like: 

id           int
info         varchar
user         varchar
start_date   datatime
end_date     datatime
resources_id int

I have to insert many rows (for example for next two years) which will have data only for working days - data from fields are just a simple copy but I don't know how to prepare insert for this kind of specific working days. Is it possible to do that at all?

And my sample dump file looks like that:

id   info   user     start_date            end_date              resource_id
31   NULL   aorlik   2018-01-04 08:00:00   2018-01-04 10:00:00   1
32   NULL   aorlik   2018-01-04 15:00:00   2018-01-04 17:00:00   1

Upvotes: 0

Views: 554

Answers (1)

Roger
Roger

Reputation: 7612

Here you go.

I create a database function that create a whole year of dates. You can finetune it according to your wishes, but you should get the idea.

It basically loops over all the days in the year, if the day happens be a sat or sun, it skips the date. You can changes this how you like.

For this to work I created a test table with 2 datetime fields (start_date, end_date). Inside the function I truncate the table, just for testing purpose.

DROP PROCEDURE IF EXISTS create_date_for_year;

DELIMITER //

CREATE PROCEDURE create_date_for_year (IN year INT)
BEGIN
    DECLARE day INT DEFAULT 1;

    SET day=1;

    TRUNCATE test; -- JUST FOR TESTING!

    WHILE day < 366 DO  
        IF WEEKDAY(MAKEDATE(year, day)) NOT IN (5,6) THEN -- skip sat and sun
            INSERT INTO TEST (start_date, end_date) VALUES ( 
                CONCAT(MAKEDATE(year, day), ' ', MAKETIME(8,0,0)),
                CONCAT(MAKEDATE(year, day), ' ', MAKETIME(10,0,0))
                );
        END IF;
        SET day = day + 1;
    END WHILE;
END;

//

CALL create_date_for_year(2018)

Upvotes: 1

Related Questions