Mayan calendar and Oracle date bug 1

[karluk]

I was reading that the Mayan "long count" calendar started on August 11, 3114 B.C. and will complete its 13th baktun on December 21, 2012. This represents a period of exactly 1,872,000 days (= 13*20*20*18*20).

Unfortunately, Oracle date datatypes have a few problems and one outright bug which prevent accurate conversions to and from Mayan dates.

select to_char(to_date('12/21/2012 ad','mm/dd/yyyy bc'),'j')
     - to_char(to_date('08/11/3114 bc','mm/dd/yyyy bc'),'j') as "days in 13 baktuns" from dual

days in 13 baktuns
------------------
           1872391

So the Oracle Julian date calculation is off by well over a year from the expected value of 1827000.

Part of the difference is attributable to the fact that August 11, 3114 BC is apparently supposed to be interpreted as a Gregorian date, whereas Oracle dates prior to October 15, 1582 are all based on the Julian calendar.

However, a much more serious problem is the fact that Oracle treats January 1, 0001 AD as being a full year (and a leap year at that) after December 31, 0001 BC, rather than the very next day.

select to_char(to_date('01/01/0001 ad','mm/dd/yyyy bc'),'j')
     - to_char(to_date('12/31/0001 bc','mm/dd/yyyy bc'),'j') as "days between 1 BC and 1 AD" from dual

days between 1 BC and 1 AD
--------------------------
                       367

I personally had never seen this problem until now, but it is apparently a known bug that Oracle has documented but never fixed. They are too worried by the potential impact on existing applications.

 

[johnherman]

Interesting enough for a star.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[SantaMufasa]

I wish that Oracle could tell us what the Mayans knew about December 21, 2012 that we don't. I know that I'm going to try to find someplace safe that day.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[johnherman]

What's significant about 12/21/12, except that it's the winter solstice? And this is Larry's Oracle, not the Oracle at Delphi.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[johnherman]

OK. I did my own research. I found this, which you may find to be useful.


For 2012

December 23 - The alternative date for the completion of the thirteenth b'ak'tun cycle in the Maya calendar, using a version of the GMT-correlation based on a JDN of 584285 (a.k.a. the "Lounsbury correlation"), which is supported by a smaller number of Mayanist researchers

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright