Function to determine the German holidays in PostgreSQL-12

Tim Holthaus/ October 7, 2020/ Dataanalyses

Big data must be enriched with other information so that observations and behavior can be analyzed exaltly. This also includes holidays because they have a strong influence on people’s behavior. So that the holidays do not have to be researched manually for each year, it is advisable to convert the logic behind the (German) holidays into a plpgsql function (see below – tested with PostgreSQL 12).

Holidays that are not defined by an exact date are dependent on Easter. The lower function returns the date for Easter Sunday for the desired year.

    create or replace function easter_sunday
    (
     year_in in integer
    )
    returns date
    as $$
    declare
     t_i integer;
     t_j integer;
     t_s integer;
     t_o integer;
     t_d integer;
     t_r integer;
     t_ogg integer;
     t_sz integer;
     t_oee integer;
     t_oss integer;
     t_day integer;
     t_month integer;
    begin
     t_i := floor(year_in / 100);
     t_j := 15 + floor((3 * t_i + 3) / 4) - floor((8 * t_i + 13) / 25);
     t_s := 2 - floor((3 * t_i + 3) / 4);
     t_o := mod(year_in, 19);
     t_d := mod((19 * t_o + t_j), 30);
     t_r := floor(t_d / 29) + (floor(t_d / 28) - floor(t_d / 29)) * floor(t_o / 11);
     t_ogg := 21 + t_d - t_r;
     t_sz := 7 - mod((year_in + floor(year_in / 4)::integer + t_s), 7.0);
     t_oee := 7 - mod(t_ogg - t_sz, 7);
     t_oss := t_ogg + t_oee;
     if (t_oss <= 31) then
      t_day := t_oss;
      t_month := 3;
     else
      t_day := t_oss - 31;
      t_month := 4;
     end if;
     return to_date(t_day || '.' || t_month || '.' || year_in, 'DD.MM.YYYY');
    end;
    $$ LANGUAGE plpgsql;

Based on this function, the following one delivers the holidays in the form of a table.

    create or replace function ger_holidays
    (
     p_year_start_in in integer,
     p_year_end_in in integer
    ) 
    returns table ( h_date date,
             h_name text,
             h_desc text)
    as  $$
    DECLARE
     va_easter_sunday date;
    begin
     for y in p_year_start_in .. p_year_end_in loop

      va_easter_sunday := easter_sunday(y);

    return query 
        select to_date('01.01.' || y, 'DD.MM.YYYY')::date, 'Neujahrestag', 'Gesetzlicher Feiertag'
        union all
        select to_date('06.01.' || y, 'DD.MM.YYYY')::date, 'Heilige Drei Könige', 'BW,BY,ST'
        union all
        select to_date('08.03.' || y, 'DD.MM.YYYY')::date, 'Internationaler Frauentag', 'BE'
        union all
        select (va_easter_sunday-interval '52' day)::date , 'Weiberfastnacht', 'kein Feiertag'
        union all
        select (va_easter_sunday-interval '48' day)::date , 'Rosenmontag', 'kein Feiertag'
        union all
        select (va_easter_sunday-interval '46' day)::date , 'Aschermittwoch', 'kein Feiertag'
        union all
        select (va_easter_sunday-interval '3' day)::date , 'Gründonnerstag', 'kein Feiertag'
        union all
        select (va_easter_sunday-interval '2' day)::date , 'Karfreitag', 'Gesetzlicher Feiertag'
        union all
        select (va_easter_sunday)::date , 'Ostersonntag', 'BB'
        union all
        select (va_easter_sunday+interval '1' day)::date , 'Ostermontag', 'Gesetzlicher Feiertag'
        union all
        select to_date('01.05.' || y, 'DD.MM.YYYY')::date, 'Tag der Arbeit', 'Gesetzlicher Feiertag'
        union all
        select (va_easter_sunday+interval '39' day)::date , 'Christi Himmelfahrt', 'Gesetzlicher Feiertag'
        union all
        select (va_easter_sunday+interval '49' day)::date , 'Pfingssonntag', 'BB'
        union all
        select (va_easter_sunday+interval '50' day)::date , 'Pfingstmontag', 'Gesetzlicher Feiertag'
        union all
        select (va_easter_sunday+interval '60' day)::date , 'Fronleichnam', 'BW,BY,HE,NW,RP'
        union all
        select to_date('15.08.' || y, 'DD.MM.YYYY')::date, 'Mariä Himmelfahrt', 'SL,BY'    
        union all
        select to_date('20.09.' || y, 'DD.MM.YYYY')::date, 'Weltkindertag', 'TH'    
        union all
        select to_date('03.10.' || y, 'DD.MM.YYYY')::date, 'Tag der Deutschen Einheit', 'Gesetzlicher Feiertag'
        union all
        select to_date('31.10.' || y, 'DD.MM.YYYY')::date, 'Reformationstag', 'BB,MV,SL,SN,TH'
        union all
        select to_date('01.11.' || y, 'DD.MM.YYYY')::date, 'Allerheiligen', 'BW,BY,NW,RP,SL'
        union all
        select to_date('11.11.' || y, 'DD.MM.YYYY')::date, 'Karnevalsbeginn', 'kein Feiertag'
        union all
        select to_date('24.12.' || y, 'DD.MM.YYYY')::date, 'Heiligabend', 'kein Feiertag'
        union all
        select to_date('25.12.' || y, 'DD.MM.YYYY')::date, '1. Weihnachtstag', 'Gesetzlicher Feiertag'
        union all
        select to_date('26.12.' || y, 'DD.MM.YYYY')::date, '2. Weihnachtstag', 'Gesetzlicher Feiertag'
        union all
        select to_date('31.12.' || y, 'DD.MM.YYYY')::date, 'Sylvester', 'kein Feiertag'
        ;
     end loop;
    end $$ LANGUAGE plpgsql;