PL/SQL Phases of the Moon
At work this past week, we kicked off a large Predictive Analytics and BI project. The predictive analytics piece will use various factors and statistical models to try and pre-determine likely areas of criminal activity around our city. Among those factors … the current and historical phase of the moon (based on date). The Oracle RDBMS contains a plethora of date and calendar related functions, but determination of the moon’s phase is not among them. Since I am the resident Oracle “guru”, the gauntlet was thrown down in front of me to come up with such a function. Not being an astronomer or mathematician, I picked up the gauntlet and ran straight to Google! I didn’t have any luck finding a PL/SQL based moon phase calculator. However, I did find a very nice algorithm that I was able to translate into a PL/SQL function at this location: lunar phase calculator. This particular algorithm uses the Julian date and the number of days in the lunar cycle to break the phases into their eight main states: New Moon (1), Waxing Crescent (2), First Quarter (3), Waxing Gibbous (4), Full Moon (5), Waning Gibbous (6), Last Quarter (7), and Waning Crescent (8). Based on this information, I actually created two functions. One function generates the numeric phase, and the other generates the text version of the phase. Here is the code for the two functions:
IS
v_age NUMBER(7,5) := 0;
v_phase NUMBER(1) := 0;
BEGIN
v_age := TRUNC(MOD(((TO_NUMBER(to_char(p_date,’J'))-2451550.1)/29.530588853) * 29.53,29.53),5);
CASE
WHEN v_age < 1.84566 THEN v_phase := 1;
WHEN v_age < 5.53699 THEN v_phase := 2;
WHEN v_age < 9.22831 THEN v_phase := 3;
WHEN v_age < 12.91963 THEN v_phase := 4;
WHEN v_age < 16.61096 THEN v_phase := 5;
WHEN v_age < 20.30228 THEN v_phase := 6;
WHEN v_age < 23.99361 THEN v_phase := 7;
WHEN v_age < 27.68493 THEN v_phase := 8;
ELSE v_phase := 1;
END CASE;
RETURN v_phase;
END;
CREATE OR REPLACE FUNCTION MOON_PHASE_TEXT(p_date DATE := SYSDATE) RETURN VARCHAR2
IS
v_phase_ind NUMBER(1) := 0;
v_phase VARCHAR2(30) := 0;
BEGIN
v_phase_ind := MOON_PHASE_NUMERIC(p_date);
CASE
WHEN v_phase_ind = 1 THEN v_phase := ‘New Moon’;
WHEN v_phase_ind = 2 THEN v_phase := ‘Waxing Crescent’;
WHEN v_phase_ind = 3 THEN v_phase := ‘First Quarter’;
WHEN v_phase_ind = 4 THEN v_phase := ‘Waxing Gibbous’;
WHEN v_phase_ind = 5 THEN v_phase := ‘Full Moon’;
WHEN v_phase_ind = 6 THEN v_phase := ‘Waning Gibbous’;
WHEN v_phase_ind = 7 THEN v_phase := ‘Last Quarter’;
WHEN v_phase_ind = 8 THEN v_phase := ‘Waning Crescent ‘;
ELSE v_phase := ‘New Moon’;
END CASE;
RETURN v_phase;
END;
If we run these functions and supply them with a date, we will get the correct phase:
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Dec 20 09:58:46 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
SQL> select MOON_PHASE_NUMERIC(’25-DEC-2009′) from dual;
MOON_PHASE_NUMERIC(’25-DEC-2009′)
———————————
3
SQL> select MOON_PHASE_TEXT(’25-DEC-2009′) from dual;
MOON_PHASE_TEXT(’25-DEC-2009′)
———————————————————————-
First Quarter
I verified the results on several moon phase calculator websites using various dates (past, present, and future). Combine these functions with some graphical representations, and you could create a nice little widget for your website.