sqlgoogle-bigquery

Determine Easter Sunday BigQuery


I am new to BigQuery, but have managed to get the following script to correctly calculate Easter Sunday for a given year, using the logic I got from Carter https://www.rmg.co.uk/stories/topics/when-easter#:~:text=The%20simple%20standard%20definition%20of,Easter%20is%20the%20next%20Sunday.

DECLARE year INT64;
DECLARE D INT64;
DECLARE E INT64;
DECLARE Q INT64;
DECLARE easter_date STRING;

-- Set year
SET year = 1998; -- Replace with the desired year

-- Step 1: Calculate D = '225' - 11 * (year MOD 19)
SET D = 225 - 11 * MOD(CAST(year AS INT64), 19);

-- Step 2: If D is greater than 50 then subtract multiples of 30 until the resulting new value of D is less than 51
WHILE D > 50 DO
SET D = D - 30;
END WHILE;

-- Step 3: If D is greater than 48 subtract 1 from it
IF D > 48 THEN
SET D = D - 1;
END IF;

-- Step 4: Calculate E = (year + CAST(FLOOR(year / 4) AS INT64) + D + 1) MOD 7
SET E = MOD(year + CAST(FLOOR(year / 4) AS INT64) + D + 1, 7);

-- Step 5: Calculate Q = D + 7 - E
SET Q = D + 7 - E;

-- Step 6: Determine the date of Easter Sunday
IF Q < 32 THEN
SET easter_date = CONCAT(CAST(year AS STRING), '-03-', CAST(Q AS STRING) );
ELSE
SET easter_date = CONCAT(CAST(year AS STRING), '-04-', CAST(Q - 31 AS STRING) );
END IF;

-- Output the result
SELECT CAST(easter_date AS DATE) AS easter_sunday;

As mentioned the script works great, however I am in the process of building a Holidays script, and need to incorporate this calculation in it. I am trying to avoid creating a function, but getting this logic into some kind of CTE, is proving difficult - I assume this is because of the loops I needed to write? Essentially I want to get Easter Sunday for a range of years (1990 - 2099), that I can add to my current date table.

Any assistance / advice please?


Solution

  • Instead of using a procedural language, you can use linear calculations to calculate more than 1 year in a single query:

    WITH
    t1 as
    (
      select year, 225 - 11 * MOD(year, 19) as d, -- 203
      from unnest(generate_array(1900, 2100)) as year
    ),
    t2 as
    (
      select
        year,
        mod(d - 21, 30) + 21 as d
      from t1
    ),
    t3 as
    (
      select 
        year,
        if(d > 48, d-1, d) as d 
      from t2
    ),
    t4 as
    (
      select 
        year, d,
        D + 7 - 
          MOD(year + CAST(FLOOR(year / 4) AS INT64) + D + 1, 7) -- this line calculates e
        as q
      from t3
    ),
    easter_list as
    (
      SELECT
        year, d,
        date(date("2000-03-01") + interval (q-1) day + interval (year - 2000) year) as x
      from t4
    )
    select *
    from easter_list