[HSQL2] Self Made Easter Sunday in SQL

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[HSQL2] Self Made Easter Sunday in SQL

Postby Villeroy » Thu Jan 11, 2018 3:02 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [HSQL2] Self Made Easter Sunday in SQL

Postby eremmel » Fri Jan 12, 2018 6:18 pm

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
eremmel
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am


Return to Base

Who is online

Users browsing this forum: No registered users and 0 guests