christopherthompson81 / pgsql_holidays Goto Github PK
View Code? Open in Web Editor NEWPackage to calculate holiday dates in PostgreSQL
License: MIT License
Package to calculate holiday dates in PostgreSQL
License: MIT License
holidays=# select * from holidays.denmark(2020,2021) where reference = 'Palm Sunday';
datestamp | reference | description | authority | day_off | observation_shifted | start_time | end_time
------------+-------------+-------------+-----------+---------+---------------------+------------+----------
2020-03-29 | Palm Sunday | Palmesøndag | national | t | f | 00:00:00 | 24:00:00
2021-03-21 | Palm Sunday | Palmesøndag | national | t | f | 00:00:00 | 24:00:00
(2 rows)
According to [1], "Palm Sunday" for 2020 is 2020-04-05.
According to Google "Palm Sunday Denmark", the date for 2021 is 2021-03-28.
When testing for public holidays from 2011, the Proclamaed Holiday raises an exception as a variable is used that does not exist (DECMBER).
Is this a straight port from python or features go into this repo? I miss the shortened days that come before holidays.
For example, in Estonia, 23. december is shortened workday (-3 hours). In Latvia the workday is shortened by 1 hour, with complicated rules.
If I submit PR with Estonian shortened days, could this be of benefit to others? And maybe other countries will follow my lead :)
Very useful and impressive. Congrats!
Here are a few corrections for my home country.
-- helper function
-- move a holiday to next monday if necessary
CREATE OR REPLACE FUNCTION holidays.bg_fix(d date)
RETURNS date
LANGUAGE sql IMMUTABLE
AS $function$
select case when extract(DOW from d)::integer in (6, 0) then holidays.find_nth_weekday_date(d, 1, 1) else d end;
$function$;
CREATE OR REPLACE FUNCTION holidays.bulgaria(p_start_year integer, p_end_year integer)
RETURNS SETOF holidays.holiday
LANGUAGE plpgsql
AS $function$
DECLARE
-- Month Constants
JANUARY INTEGER := 1;
FEBRUARY INTEGER := 2;
MARCH INTEGER := 3;
APRIL INTEGER := 4;
MAY INTEGER := 5;
JUNE INTEGER := 6;
JULY INTEGER := 7;
AUGUST INTEGER := 8;
SEPTEMBER INTEGER := 9;
OCTOBER INTEGER := 10;
NOVEMBER INTEGER := 11;
DECEMBER INTEGER := 12;
-- Weekday Constants
SUNDAY INTEGER := 0;
MONDAY INTEGER := 1;
TUESDAY INTEGER := 2;
WEDNESDAY INTEGER := 3;
THURSDAY INTEGER := 4;
FRIDAY INTEGER := 5;
SATURDAY INTEGER := 6;
WEEKEND INTEGER[] := ARRAY[0, 6];
-- Primary Loop
t_years INTEGER[] := (SELECT ARRAY(SELECT generate_series(p_start_year, p_end_year)));
-- Holding Variables
t_year INTEGER;
t_datestamp DATE;
christmas_start DATE;
christmas_offsets integer[];
christmas_offset integer;
t_holiday holidays.holiday%rowtype;
BEGIN
FOREACH t_year IN ARRAY t_years
LOOP
-- Return an empty list prior to the current country existing
IF t_year < 1990 THEN
RETURN;
END IF;
-- New Year's Day
t_holiday.datestamp := holidays.bg_fix(make_date(t_year, JANUARY, 1));
t_holiday.description := 'Нова година';
RETURN NEXT t_holiday;
-- Liberation Day
t_holiday.datestamp := holidays.bg_fix(make_date(t_year, MARCH, 3));
t_holiday.description := 'Ден на Освобождението на България от османско иго';
RETURN NEXT t_holiday;
-- International Workers' Day
t_holiday.datestamp := holidays.bg_fix(make_date(t_year, MAY, 1));
t_holiday.description := 'Ден на труда и на международната работническа солидарност';
RETURN NEXT t_holiday;
-- Saint George's Day
t_holiday.datestamp := holidays.bg_fix(make_date(t_year, MAY, 6));
t_holiday.description := 'Гергьовден, Ден на храбростта и Българската армия';
RETURN NEXT t_holiday;
-- Bulgarian Education and Culture and Slavonic Literature Day
t_holiday.datestamp := holidays.bg_fix(make_date(t_year, MAY, 24));
t_holiday.description := 'Ден на българската просвета и култура и на славянската писменост';
RETURN NEXT t_holiday;
-- Unification Day
t_holiday.datestamp := holidays.bg_fix(make_date(t_year, SEPTEMBER, 6));
t_holiday.description := 'Ден на Съединението';
RETURN NEXT t_holiday;
-- Independence Day
t_holiday.datestamp := holidays.bg_fix(make_date(t_year, SEPTEMBER, 22));
t_holiday.description := 'Ден на Независимостта на България';
RETURN NEXT t_holiday;
-- National Awakening Day - but still a business day
-- t_holiday.datestamp := holidays.bg_fix(make_date(t_year, NOVEMBER, 1));
-- t_holiday.description := 'Ден на народните будители';
-- RETURN NEXT t_holiday;
-- Christmas
christmas_start := make_date(t_year, DECEMBER, 24);
christmas_offsets := case extract(DOW from christmas_start)::integer
when THURSDAY then array[0, 1, 4] -- thursday -> thursday, friday, monday
when FRIDAY then array[0, 3, 4] -- friday -> friday, monday, tuesday
when SATURDAY then array[2, 3, 4] -- saturday -> monday, tuesday, wednesday
when SUNDAY then array[1, 2, 3] -- sunday -> monday, tuesday, wednesday
else array[0, 1, 2] end;
foreach christmas_offset in array christmas_offsets loop
t_holiday.description := 'Рождество Христово';
t_holiday.datestamp := christmas_start + christmas_offset;
RETURN NEXT t_holiday;
end loop;
-- Easter
t_datestamp := holidays.easter(t_year, p_method => 'EASTER_ORTHODOX');
t_holiday.datestamp := t_datestamp - '2 Days'::INTERVAL;
t_holiday.description := 'Разпети петък';
RETURN NEXT t_holiday;
t_holiday.datestamp := t_datestamp + '1 Days'::INTERVAL;
t_holiday.description := 'Велики понеделник';
RETURN NEXT t_holiday;
END LOOP;
END;
$function$;
Hi,
for Windows there is currently no strait forward way to “build” this extension, due to the build script being bash based. Also, PGXN does not support Windows (see FAQ: What about Windows?).
Since the build script just copies all the pgsql files into one sql file and does not actually compile anything, it would be a shame to exclude windows users from this pristine extension for no good reason.
For Windows users, I uploaded the finished “build” sql file and matching control file here: pgsql_holidays.zip
Copy them into your PostgreSQL install folder under C:\Program Files\PostgreSQL\XXX\share\extension.
Then run:
CREATE EXTENSION IF NOT EXISTS holidays
It would be nice to have something automatic to build und publish the extension under Releases.
That way anyone interested could use the extension without the hassle of running the Linux only build process.
Cheers! 🍻
Hi,
nice package! I found some wrong data related to my home country, Croatia, so I wanted you to know.
Last year, the government decided to change some state holidays - https://publicholidays.com.hr/2020-dates/
For fixing this issue, the holiday on the 25th of June should be deleted and a new one should be added on the 18th of November. This one should be named 'Dan sjećanja'.
Keep up the good work!
Have you thought about packaging this as an extension for Postgres?
Doing so can make distribution easier, and increase uptake.
You also can benefit from publishing this as an extension on https://pgxn.org/ for example.
When asking for holidays across multiple years for example, SELECT * FROM holidays.south_africa(2021, 2022) ORDER BY 1
, the holidays that are on a Sunday that get observed on the following day are being duplicated.
A sample of the output is below.
datestamp | reference | description |
---|---|---|
2021-01-01 | New Year's Day | New Year's Day |
2021-03-21 | Human Rights Day | Human Rights Day |
2021-03-22 | Human Rights Day | Human Rights Day (Observed) |
2021-03-22 | Human Rights Day | Human Rights Day (Observed) |
2021-04-02 | Good Friday | Good Friday |
The observed row is duplicated for every year that is in the loop. In this case once. If the end year was 2025, the observed row would be duplicated 4 times.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.