Page 1 of 1

[HSQL2] Self Made Easter Sunday in SQL

Posted: Thu Jan 11, 2018 3:02 pm
by Villeroy
Problem: HSQL has no Easter function to calculate variable christian holydays such as Easter, Pentecost, Good Friday.
Solution: https://stackoverflow.com/questions/260 ... ter-sunday describes how to calculate Easter Sunday for any given year number in 10 simple steps. I transfered these simple functions to Calc and substituted the variables until I got a month formula depending on the year only and a day formula depending on month and year only.
Caveat: With the embedded HSQL1.8 some dates differ by -7 days. This seems to be a bug in HSQL1.8. With HSQL2.3.4 and 2.4.x the query results for all years between 1584 and 2100 are identical to Calc's EASTERSUNDAY.

The uploaded zip archive http://www.mediafire.com/file/9979c8ru5 ... undays.zip contains my test database with HSQL2.4.1 driver and a Base document with auto-install macro. Extract to a trusted directory and open EasterSundays.odb. The auto-install macro should report a successfull connection of the document with database and driver. Two issues with the HSQL 2.4.1 driver and workarounds are described in a README form embedded in the database.

Having a list of year numbers "Years"."Y", I calculate month and day numbers like this:

Code: Select all

SELECT "Y", "M", <day formula> AS "D" FROM (SELECT "Y", <month formula> AS "M" FROM "Years") AS "Months"
Having a list of dates "Table"."Dates", you can use this variant to calculate a short list of Easter Sundays for each year. This is the same query as above but with distinct year numbers derived from a column of dates:

Code: Select all

SELECT "Y", "M", <day formula> AS "D" FROM (SELECT "Y", <month formula> AS "M" FROM (SELECT DISTINCT YEAR("Dates") AS "Y" FROM "Table") AS "Years") AS "Months"
The <month formula> referring to year "Y" is:

Code: Select all

FLOOR((MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) - FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + MOD(2 * MOD(FLOOR("Y"/100),4) + 2 * FLOOR(MOD("Y",100)/4) - MOD(MOD("Y",100),4) - MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) + FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + 32,7) + 90)/25)
The <day formula> referring to year "Y" and month "M" is:

Code: Select all

MOD(MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) - FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + MOD(2 * MOD(FLOOR("Y"/100),4) + 2 * FLOOR(MOD("Y",100)/4) - MOD(MOD("Y",100),4) - MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30) + FLOOR((MOD("Y",19)+11*MOD(19 * MOD("Y",19) + FLOOR("Y"/100) - FLOOR(FLOOR("Y"/100)/4) - FLOOR((8*FLOOR("Y"/100)+13)/25) + 15,30))/319) + 32,7) + "M" + 19,32)
Usage: Compose a query in SQL view adjusting the source of year numbers to your own database. With alias "Y" you don't have to adjust the complex formulas.
Save the working query as a view because in this particular case you can do more with a view.
In the example database I UNION merged some variable christian holidays (Good Friday, Easter, Pentecost, Corp. Chr.) with a list of fixed holydays (national, Xmas etc.):

Code: Select all

SELECT dateadd('day',-3,CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE))AS "DT", 'Good Friday' AS "DESCRIPTION" FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE), 'Easter Sunday' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT DATEADD('day',39, CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE)), 'Ascension Day' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT DATEADD('day', 49, CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE)), 'Pentecost Sunday' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT DATEADD('day', 60, CAST("Y" ||'-'|| "M" ||'-'|| "D" AS DATE)), 'Corpus Christ (cath.)' FROM "vEasterSundays" WHERE "Y" BETWEEN 2010 AND 2018
UNION SELECT CAST("vEasterSundays"."Y" ||'-'|| "FH"."M" ||'-'|| "FH"."D" AS DATE), DESCR FROM "FixedHolydays" AS "FH", "vEasterSundays" WHERE NOT "FH"."IGN" AND "Y" BETWEEN 2010 AND 2018

Re: [HSQL2] Self Made Easter Sunday in SQL

Posted: Fri Jan 12, 2018 6:18 pm
by eremmel
Villeroy, interesting and useful exercise. Check this post for putting the calculation into a database function (will not work with embedded 1.8 HSQLDB) and the remark that there are at least three Easters to celebrate each year.