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:

CREATE OR REPLACE FUNCTION MOON_PHASE_NUMERIC(p_date DATE := SYSDATE) RETURN NUMBER
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.

Comments are closed.