Skip to content

christopherthompson81/pgsql_holidays

master
Switch branches/tags
Code

Latest commit

 

Git stats

Files

Permalink
Failed to load latest commit information.
Type
Name
Latest commit message
Commit time
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

pgsql_holidays

Package to calculate holiday dates in PostgreSQL

Installation:

There are files provided which will create schemas and functions in your database. The holidays package is intended to reside in a new "holidays" schema.

  1. Create the holidays schema using the files in /db_setup/holidays_schema.pgsql
  2. Create the "holiday" type in the new schema using /db_setup/holiday_type.pgsql
  3. Add the utility functions necessary for your use case. You will most likely need "easter.pgsql", and "find_nth_weekday_date.pgsql".
  4. Add your country function. For example: "countries/canada.pgsql"

Alternatively, you can use the python loader.

  1. Create a postgresql_config.json file using postgresql_config.example.json as a template

  2. Install pipenv

     pip install pipenv
    
  3. Use pipenv to install the prerequisite python modules

     pipenv install
    
  4. Run the loader

    pipenv run python .\build_holidays_schema.py
    
  5. Chinese, Hebrew, Hijri, Hindu, and Jalali calendar functions require another of my libraries: calendar_converter_pgsql

Usage:

You can select holidays from the new schema for your country using the following syntax. Where there are no sub-regions in a country, the parameter is omitted from the call.

SELECT * FROM holidays.canada('ON', 2020, 2020);

The query will return the results:

datestamp       description                authority      day_off      observation_shifted   start_time   end_time        
[DATE]          [TEXT]                     [ENUM]         [BOOLEAN]    [BOOLEAN]             [TIME]       [TIME]
------------    -----------------------    -----------    ---------    -------------------   ----------   ----------
"2020-01-01"	"New Year's Day"           "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-02-17"	"Family Day"               "provincial"    true        false                 "00:00:00"   "24:00:00"
"2020-04-10"	"Good Friday"              "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-05-18"	"Victoria Day"             "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-07-01"	"Canada Day"               "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-08-03"	"Civic Holiday"            "provincial"    true        false                 "00:00:00"   "24:00:00"
"2020-09-07"	"Labour Day"               "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-11-11"	"Remembrance Day"          "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-12-25"	"Christmas Day"            "federal"       true        false                 "00:00:00"   "24:00:00"
"2020-12-28"	"Boxing Day (Observed)"    "federal"       true        true                  "00:00:00"   "24:00:00"

A convienience "by country" function is also provided which accepts many name variations and defines a default region.

SELECT * FROM holidays.by_country('canada', 2020, 2020);

The above query would also produce the same output.

To Do

Cross-port the knowledge from the npm / javascript libraries for the same purpose. It uses a declarative method (which may be concurrently recorded here), implements periods, and covers additional countries:

Fill in missing information related to partial holidays, or non-holiday, but special dates:

Feedback

I openly solicit pull requests and issues as feedback to make this package better. The port from Python was naive and I'm only intimately knowledgable about my own country's holidays (Canada). I expect many corrections and enhancements are necessary.

Ported From Credits / Attributions

About

Package to calculate holiday dates in PostgreSQL

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published