Initial commit
[postgresql/bank_holidays] / bank_holidays.sql
1 -- Bank holiday prediction functions 22/03/2013 Glyn Astill <glyn@8kb.co.uk>\r
2 -- Mostly plagiarised from plpgsql functions posted to PostgreSQL mailing list\r
3 -- on 15/05/2007 by Gary Stainburn (gary.stainburn@ringways.co.uk) and converted\r
4 -- to plain sql. See:\r
5 --      http://www.postgresql.org/message-id/flat/200705151509.41320.gary.stainburn@ringways.co.uk#200705151509.41320.gary.stainburn@ringways.co.uk\r
6 --      http://www.postgresql.org/message-id/attachment/21954/bank_holidays.sql\r
7 \r
8 DROP FUNCTION IF EXISTS public.calculate_easter_sunday(integer);\r
9 CREATE OR REPLACE FUNCTION public.calculate_easter_sunday(integer) \r
10 RETURNS date AS \r
11 $BODY$    \r
12     SELECT \r
13     CASE \r
14         WHEN (((((($1%19)*19)+24)%30)+(((($1%4)*2)+(($1%7)*4)+((((($1%19)*19)+24)%30)*6)+5)%7)) < 10) THEN\r
15                 ($1 || '-03-' || (((($1%19)*19)+24)%30)+(((($1%4)*2)+(($1%7)*4)+((((($1%19)*19)+24)%30)*6)+5)%7)+22)::date\r
16         ELSE\r
17                 ($1 || '-04-' || (((($1%19)*19)+24)%30)+(((($1%4)*2)+(($1%7)*4)+((((($1%19)*19)+24)%30)*6)+5)%7)-9)::date\r
18     END;\r
19 $BODY$ \r
20 LANGUAGE SQL IMMUTABLE;\r
21 \r
22 GRANT EXECUTE ON FUNCTION public.calculate_easter_sunday(integer) TO public;\r
23 \r
24 --\r
25 \r
26 DROP FUNCTION IF EXISTS public.calculate_new_year(integer);\r
27 CREATE OR REPLACE FUNCTION public.calculate_new_year(integer) \r
28 RETURNS date AS \r
29 $BODY$    \r
30     SELECT \r
31     CASE extract(dow FROM ($1 || '-01-01')::date)\r
32         WHEN 0 THEN ($1 || '-01-02')::date\r
33         WHEN 6 THEN ($1 || '-01-03')::date\r
34         ELSE ($1 || '-01-01')::date\r
35     END;\r
36 $BODY$ \r
37 LANGUAGE SQL IMMUTABLE;\r
38 \r
39 GRANT EXECUTE ON FUNCTION public.calculate_new_year(integer) TO public;\r
40 \r
41 --\r
42 \r
43 DROP FUNCTION IF EXISTS public.calculate_christmas_day(integer);\r
44 CREATE OR REPLACE FUNCTION public.calculate_christmas_day(integer) \r
45 RETURNS date AS \r
46 $BODY$    \r
47     SELECT \r
48     CASE extract(dow FROM ($1 || '-12-25')::date)\r
49         WHEN 0 THEN ($1 || '-12-27')::date\r
50         WHEN 6 THEN ($1 || '-12-28')::date\r
51         ELSE ($1 || '-12-25')::date\r
52     END;\r
53 $BODY$ \r
54 LANGUAGE SQL IMMUTABLE;\r
55 \r
56 GRANT EXECUTE ON FUNCTION public.calculate_christmas_day(integer) TO public;\r
57 --\r
58 \r
59 DROP FUNCTION IF EXISTS public.calculate_boxing_day(integer);\r
60 CREATE OR REPLACE FUNCTION public.calculate_boxing_day(integer) \r
61 RETURNS date AS \r
62 $BODY$    \r
63     SELECT \r
64     CASE extract(dow FROM ($1 || '-12-26')::date)\r
65         WHEN 0 THEN ($1 || '-12-27')::date\r
66         WHEN 6 THEN ($1 || '-12-28')::date\r
67         ELSE ($1 || '-12-26')::date\r
68     END;\r
69 $BODY$ \r
70 LANGUAGE SQL IMMUTABLE;\r
71 \r
72 GRANT EXECUTE ON FUNCTION public.calculate_boxing_day(integer) TO public;\r
73 \r
74 --\r
75 \r
76 DROP FUNCTION IF EXISTS public.calculate_may_day(integer);\r
77 CREATE OR REPLACE FUNCTION public.calculate_may_day(integer) \r
78 RETURNS date AS \r
79 $BODY$    \r
80     SELECT \r
81     CASE \r
82         WHEN extract(dow FROM ($1 || '-05-01')::date) < 2 THEN ($1 || '-05-' || 2-extract(dow FROM ($1 || '-05-01')::date))::date\r
83         ELSE ($1 || '-05-' || 9-extract(dow FROM ($1 || '-05-01')::date))::date\r
84     END;\r
85 $BODY$ \r
86 LANGUAGE SQL IMMUTABLE;\r
87 \r
88 GRANT EXECUTE ON FUNCTION public.calculate_may_day(integer)  TO public;\r
89 \r
90 --\r
91 \r
92 DROP FUNCTION IF EXISTS public.calculate_spring_bank_holiday(integer);\r
93 CREATE OR REPLACE FUNCTION public.calculate_spring_bank_holiday(integer) \r
94 RETURNS date AS \r
95 $BODY$    \r
96     SELECT \r
97     CASE extract(dow FROM ($1 || '-05-31')::date)\r
98         WHEN 0 THEN ($1 || '-05-25')::date\r
99         ELSE ($1 || '-05-' || 32-extract(dow FROM ($1 || '-05-31')::date))::date\r
100     END;\r
101 $BODY$ \r
102 LANGUAGE SQL IMMUTABLE;\r
103 \r
104 GRANT EXECUTE ON FUNCTION public.calculate_spring_bank_holiday(integer) TO public;\r
105 \r
106 --\r
107 \r
108 DROP FUNCTION IF EXISTS public.calculate_whitsun(integer);\r
109 CREATE OR REPLACE FUNCTION public.calculate_whitsun(integer) \r
110 RETURNS date AS \r
111 $BODY$    \r
112     SELECT public.calculate_spring_bank_holiday($1) ;\r
113 $BODY$ \r
114 LANGUAGE SQL IMMUTABLE;\r
115 \r
116 GRANT EXECUTE ON FUNCTION public.calculate_whitsun(integer)  TO public;\r
117 \r
118 --\r
119 \r
120 DROP FUNCTION IF EXISTS public.calculate_summer_bank_holiday(integer);\r
121 CREATE OR REPLACE FUNCTION public.calculate_summer_bank_holiday(integer) \r
122 RETURNS date AS \r
123 $BODY$    \r
124     SELECT \r
125     CASE extract(dow FROM ($1 || '-08-31')::date)\r
126         WHEN 0 THEN ($1 || '-08-25')::date\r
127         ELSE ($1 || '-08-' || 32-extract(dow FROM ($1 || '-08-31')::date))::date\r
128     END;\r
129 $BODY$ \r
130 LANGUAGE SQL IMMUTABLE;\r
131 \r
132 GRANT EXECUTE ON FUNCTION public.calculate_summer_bank_holiday(integer) TO public;\r
133 \r
134 --\r
135 \r
136 DROP TYPE IF EXISTS public.bank_holidays CASCADE;\r
137 CREATE TYPE public.bank_holidays AS\r
138 (year integer, description varchar, bank_holiday date);\r
139 \r
140 DROP FUNCTION IF EXISTS public.calculate_bank_holidays(integer);\r
141 CREATE OR REPLACE FUNCTION public.calculate_bank_holidays(integer) \r
142 RETURNS SETOF public.bank_holidays AS \r
143 $BODY$\r
144     SELECT $1, 'New Years Day', public.calculate_new_year($1)\r
145     UNION\r
146     SELECT $1, 'Good Friday', (public.calculate_easter_sunday($1)-'2 days'::interval)::date\r
147     UNION\r
148     SELECT $1, 'Easter Monday', (public.calculate_easter_sunday($1)+'1 days'::interval)::date\r
149     UNION \r
150     SELECT $1, 'May Day', public.calculate_may_day($1)\r
151     UNION \r
152     SELECT $1, 'Spring Bank Holiday', public.calculate_spring_bank_holiday($1)\r
153     UNION \r
154     SELECT $1, 'Summer Bank Holiday', public.calculate_summer_bank_holiday($1)\r
155     UNION \r
156     SELECT $1, 'Christmas Day', public.calculate_christmas_day($1)\r
157     UNION \r
158     SELECT $1, 'Boxing Day', public.calculate_boxing_day($1)\r
159     ORDER BY 3;\r
160 $BODY$\r
161 LANGUAGE SQL IMMUTABLE;\r
162 \r
163 GRANT EXECUTE ON FUNCTION public.calculate_bank_holidays(integer) TO public;\r
164 \r
165 --\r
166 \r
167 DROP FUNCTION IF EXISTS public.calculate_bank_holidays(integer, integer);\r
168 CREATE OR REPLACE FUNCTION public.calculate_bank_holidays(integer, integer) \r
169 RETURNS SETOF bank_holidays AS \r
170 $BODY$\r
171     SELECT (public.calculate_bank_holidays(generate_series)).* FROM generate_series($1, $2);\r
172 $BODY$\r
173 LANGUAGE SQL IMMUTABLE;\r
174 \r
175 GRANT EXECUTE ON FUNCTION public.calculate_bank_holidays(integer, integer) TO public;\r
176 \r
177 --\r
178 \r
179 DROP FUNCTION IF EXISTS public.detail_bank_holiday(date);\r
180 CREATE OR REPLACE FUNCTION public.detail_bank_holiday(date) \r
181 RETURNS SETOF bank_holidays AS \r
182 $BODY$\r
183     SELECT * FROM public.calculate_bank_holidays(extract(year FROM $1)::integer)\r
184     WHERE bank_holiday = $1;\r
185 $BODY$\r
186 LANGUAGE SQL IMMUTABLE;\r
187 \r
188 GRANT EXECUTE ON FUNCTION public.detail_bank_holiday(date) TO public;\r
189 \r
190 --\r
191 \r
192 DROP FUNCTION IF EXISTS public.is_bank_holiday(date);\r
193 CREATE OR REPLACE FUNCTION public.is_bank_holiday(date) \r
194 RETURNS boolean AS \r
195 $BODY$\r
196     SELECT EXISTS(SELECT 1 FROM public.calculate_bank_holidays(extract(year FROM $1)::integer)\r
197     WHERE bank_holiday = $1);\r
198 $BODY$\r
199 LANGUAGE SQL IMMUTABLE;\r
200 \r
201 GRANT EXECUTE ON FUNCTION public.is_bank_holiday(date) TO public;\r
202 \r
203 --\r
204 \r
205 DROP FUNCTION IF EXISTS public.count_bank_holidays(date, date);\r
206 CREATE OR REPLACE FUNCTION public.count_bank_holidays(date, date) \r
207 RETURNS bigint AS \r
208 $BODY$\r
209     SELECT count(*) FROM public.calculate_bank_holidays(extract(year FROM $1)::integer, extract(year FROM $2)::integer)\r
210     WHERE bank_holiday BETWEEN $1 AND $2;\r
211 $BODY$\r
212 LANGUAGE SQL IMMUTABLE;\r
213 \r
214 GRANT EXECUTE ON FUNCTION public.count_bank_holidays(date, date) TO public;\r
215 \r
216 --