Monday, October 6, 2014

Astronomical Calculations in SQL - Full Moon on Halloween

My daughter came to me yesterday and asked: Do you know that Full Moon on Halloween happens once in 800 years?

I said: - It's impossible!!!
- Google it! - replied she.

But think I'm better astronomer than Google and can calculate it by myself.

I've started from the time Moon needs to circle the Earth. That is 29.530588853 days.
Than I took the time of the next Full Moon, which is 2014-10-08 10:52am UTC and wrote SQL query:




DECLARE @Period datetime2 =  
    '2014-10-08 10:52am';
DECLARE @Days INT = 29;
DECLARE @Minutes Numeric(38,9) = 764.047948320;

;with ctr as (
    SELECT CAST(
       ROW_NUMBER()
       over(order by message_id)-24907 as NUMERIC
       ) as rn
    FROM sys.messages
), FM AS (
    SELECT DATEADD(
       DAY,@Days*rn,
       DATEADD(MINUTE,@Minutes*rn,@Period)
       ) as Period_Dt
    FROM ctr)
SELECT TOP 348 Period_Dt
FROM FM
WHERE Month(Period_Dt) = 10
    and DAY(Period_Dt) = 31;

That query should work in SQL 2008R2 and higher.

Accordingly to my calculations the next Full Moon on Halloween will be in 2020.

By eliminating the WHERE and TOP clauses you can get just Full Moon timing for the last 2000 years and as far as SQL Server will allow you.

No comments:

Post a Comment