[HSQL2] Self Made Easter Sunday in SQL

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[HSQL2] Self Made Easter Sunday in SQL

Post 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: [HSQL2] Self Made Easter Sunday in SQL

Post 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply