Czech holidays was valid 2022-09-03. Slovak holidays was valid 2022-11-04.
České svátky byly validní 9.3.2022 Slovenské svátky byly validní 11.4.2022
For mssql version, see https://gist.github.com/kondelik/0fac62901714e6cfc59decbbb0ab9576
drop procedure if exists fill_calendar;
drop function if exists easter;
drop table if exists calendar;
create table calendar (
`id` integer primary key, -- year*10000+month*100+day - for example, 20220309
`date` date not null,
`year` integer not null,
`month` integer not null, -- 1 to 12
`day` integer not null, -- 1 to 31
`quarter` integer not null, -- 1 to 4
`week` integer not null, -- 1 to 52/53
`weekend` bit(1) not null,
`holiday_cs` bit(1) not null,
`holiday_sk` bit(1) not null,
unique td_ymd_idx (`year`,`month`,`day`),
unique td_dbdate_idx (`date`)
);
delimiter $$
# https://github.com/jweiher/mysql-easter/blob/master/easter.sql
create function `easter`(X integer) returns date
begin
declare K,M,S,A,D,R,OG,SZ,OE,OS int;
declare easterdate date;
set K = X div 100;
set M = 15 + (3*K + 3) div 4 - (8*K + 13) div 25;
set S = 2 - (3*K + 3) div 4;
set A = X mod 19;
set D = (19*A + M) mod 30;
set R = (D + A DIV 11) div 29;
set OG = 21 + D - R ;
set SZ = 7 - (X + X div 4 + S) mod 7;
set OE = 7 - (OG - SZ) mod 7;
set OS = OG + OE;
set easterdate = date_add(concat(X, '-03-01'), interval OS-1 day);
return easterdate;
end$$
create procedure fill_calendar(startdate date, stopdate date)
begin
declare currentdate date;
declare currentyear int;
set currentdate = startdate;
while currentdate < stopdate
do
insert into calendar values (
(year(currentdate) * 10000) + (month(currentdate) * 100) + day(currentdate),
currentdate,
year(currentdate),
month(currentdate),
day(currentdate),
quarter(currentdate),
weekofyear(currentdate),
case dayofweek(currentdate) when 1 then 1 when 7 then 1 else 0 end,
case month(currentdate)
when 1 then
case day(currentdate) when 1 then 1 else 0 # Nový rok
end
when 5 then
case day(currentdate)
when 1 then 1 # Svátek práce
when 8 then 1 # Den osvobození od fašismu
else 0
end
when 7 then
case day(currentdate)
when 5 then 1 # Den slovanských věrozvěstů Cyrila a Metoděje
when 6 then 1 # Den upálení mistra Jana Husa
else 0
end
when 9 then
case day(currentdate)
when 28 then 1 # Den české státnosti
else 0
end
when 10 then
case day(currentdate)
when 28 then 1 # Den vzniku samostatného československého státu
else 0
end
when 11 then
case day(currentdate)
when 17 then 1 # Den boje za svobodu a demokracii
else 0
end
when 12 then
case day(currentdate)
when 24 then 1 # Štědrý den
when 25 then 1 # První vánoční svátek
when 26 then 1 # Druhý vánoční svátek
else 0
end
else 0
end,
case month(currentdate)
when 1 then
case day(currentdate)
when 1 then 1 # Den vzniku Slovenské republiky
when 6 then 1 # Zjevení Páně (tři králové a vánoční svátek pravoslavných křesťanů)
else 0
end
when 5 then
case day(currentdate)
when 1 then 1 # Svátek práce
when 8 then 1 # Den vítězství nad fašismem
else 0
end
when 7 then
case day(currentdate)
when 5 then 1 # Svátek sv. Cyrila a Metoděje a Den zahraničních Slováků
else 0
end
when 8 then
case day(currentdate)
when 29 then 1 # Výročí Slovenského národního povstání
else 0
end
when 9 then
case day(currentdate)
when 1 then 1 # Den ústavy SR
when 15 then 1 # Panna Marie Sedmibolestná
else 0
end
when 11 then
case day(currentdate)
when 1 then 1 # Svátek všech svatých
when 17 then 1 # Den boje za svobodu a demokracii
else 0
end
when 12 then
case day(currentdate)
when 24 then 1 # Štědrý den
when 25 then 1 # První vánoční svátek
when 26 then 1 # Druhý vánoční svátek
else 0
end
else 0
end
);
set currentdate = date_add(currentdate, interval 1 day);
end while;
set currentyear = year(startdate);
while currentyear <= year(stopdate)
do
update `calendar`
set `holiday_cs` = 1,
`holiday_sk` = 1
where date = date_add(easter(currentyear), INTERVAL -2 DAY) # Velký pátek
or date = date_add(easter(currentyear), INTERVAL 1 DAY); # Velikonoční pondělí
set currentyear = currentyear + 1;
end while;
end$$
delimiter ;
truncate calendar;
call fill_calendar('2022-1-1', '2100-12-31');Generate once for lifetime and just use.
When you need to find workdays:
select `calendar`.`date`
from `calendar`
inner join `tasks` on `tasks`.`date` = `calendar`.`date` and `calendar`.`holiday_cs` = 0 and `calendar`.`weekend` = 0
where `tasks`.`date` between '2022-1-3' and '2022-31-3'holidays but also weekend:
select `date` as `oh frak`
from `calendar`
where `holiday_cs` = 1 and `weekend` = 1 and `year` = 2022;Etc...
Please note mysql is not my db of choice, maybe something can be written in more optimised / elegant way. I dont care, its 'generate once & use as long as you can'.
Author of easter(year) function is jwaiher (https://github.com/jweiher/mysql-easter).
Rest is BSD licence (as in 'you can do whatever you want commercially or not, but you cant sue me')
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.