[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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25436
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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 988
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest