How to calculate the date of Easter in BigQuery
Easter is a movable feast, and its date changes every year. If you work with data — whether you are analyzing seasonal traffic trends, comparing campaign performance, or planning marketing activities — it can be useful to know the exact dates of the Easter holidays. That is where a simple SQL script for BigQuery can come in handy.
How is the date of Easter determined?
Easter is a movable feast — unlike Christmas, it does not fall on the same date each year. To put it simply, Easter Sunday is celebrated on the first Sunday after the first full moon following the spring equinox (approximately March 21).
To calculate the exact date in our solar (Gregorian) calendar, we need to combine the lunar cycle (the phases of the Moon) with the calendar year. This process is historically known as computus paschalis (Latin for “the calculation of Easter”), and often simply referred to as the Computus. Because the lunar and solar cycles are not aligned, mathematician Carl Friedrich Gauss created a formula that translates the rules based on the full moon and equinox into an actual date in the Gregorian calendar.
The result is a surprisingly elegant set of calculations — widely known and available online in languages like Python, standard SQL, and more. However, when I needed a version for BigQuery, I could not find a ready-to-use script to copy & paste. But now you don’t have to search — we’ve prepared it for you!
What does our script do?
Using the Computus algorithm, it calculates the exact date of Easter Sunday for each year (by default between 2024 and 2100, though you can easily change the year range). It then also outputs the related Easter dates in the YYYY-MM-DD format:
- Good Friday (2 days before),
- Holy Saturday (1 day before),
- Easter Monday (1 day after).
What is it useful for?
- Filtering specific days in GA4 data – this helps remove holiday-related fluctuations from the analysis of regular trends (e.g. traffic, conversions).
- Tagging holidays in dashboards – this allows you to add holidays as flags in dashboards (e.g. Looker Studio, Power BI) connected to BigQuery.
- More accurate campaign comparisons – Easter holidays can be used as a segment when evaluating campaign performance — in GA4, Google Ads, or other systems connected to BigQuery.
How does the script work?
The BigQuery script generates a list of years using GENERATE_ARRAY and then applies a set of mathematical operations (MOD, CONCAT, FORMAT) to calculate the exact date of Easter using a well-known algorithm.
- Generating the years
In the first step (WITH years AS ...), the script creates a list of years for which we want to calculate Easter (by default, 2024 to 2100).
- Calculating intermediate steps (mods)
Using several MOD operations, the script calculates intermediate variables a, b, c, d, and e, which are part of Gauss’s formula.
For example, a = year MOD 19 represents the "Golden Number" in the lunar cycle.
- Determining the date of Easter Sunday
Using CASE WHEN logic, the script calculates the final date in yyyymmdd format, depending on whether Easter falls in March or April.
- Adding the surrounding days
With CROSS JOIN UNNEST(GENERATE_ARRAY(-2, 1)), the script adds the related days:
- Friday (−2)
- Saturday (−1)
- Sunday (0)
- Monday (+1)
- Final output
The script returns dates in YYYY-MM-DD format that correspond to Easter holidays for each year — ready to be joined to other tables or time dimensions.
You can find the full query on our GitHub.
Hopefully, Easter will never catch you off guard in your data again. Let us know — for example on LinkedIn — how you used the script in your own projects.
