copyright 2003 by Peter Gulutzan and Trudy Pelzer
This article is a supplement to Oracle's documentation. Oracle doesn't bother to document details about how arithmetic works with the DATE data type, so we will. This information affects anyone who uses old dates, including astronomers, historians, and genealogists. It should also interest anyone who has ever wondered why the minimum DATE value in Oracle is supposedly January 1 4712 BC. (Which is wrong, by the way.)
The calendar that we're familiar with has several rules that we all learn at an early age: twelve months in a year, thirty days hath September, and so on. Only two rules are confusing and have variants: the "year-zero" rule and the "leap-year-calculation" rule. So let's state those two rules clearly.
The "year-zero" rule:
There is no year zero with the common "BC/AD" convention. Instead, the year after 1 BC is 1 AD. However, astronomers prefer to use a different convention. Instead of "BC" and "AD" astronomers prefer "-" and "+", and include a year zero. The following chart shows a sequence of years around the time of Christ's birth, according to the two conventions:
|COMMON-CONVENTION YEAR||ASTRONOMICAL-CONVENTION YEAR|
|1 AD||+1 (or just 1, the + can be omitted)|
As shown in the chart, any AD year is equal to the astronomical year, and any BC year is equal to the astronomical year minus one. The use of "-" instead of "BC" is a signal that you are looking at an astronomical date.
The "leap-year calculation" rule:
In the Julian calendar, there is a simple rule that says that every year that is divisible by four is a leap year. This rule works with both positive and negative dates if the astronomical convention is used. However, in our familiar Gregorian calendar, the rule is slightly more complex: Every year that is divisible by four is a leap year, except that every year that is (a) divisible by 100 and (b) indivisible by 400, is not a leap year. The effect of the Julian rule is that a 400-year period will have (365 times 400 plus 100) 146100 days, while the effect of the Gregorian rule is that a 400-year period will have (365 times 400 plus 100 minus 3) 146097 days -- a difference of only 0.75 days per century, but a difference nonetheless.
There is also an extra complication. Every country that switched from the Julian to the Gregorian calendar (which by now is all of the West) has had to discard at least ten days during the switch. To see how this works, let's look at a calendar snippet from the month of October 1582, the time of the original Julian-to-Gregorian switch.
Date Monday October 1, 1582 ... nothing odd about this Tuesday October 2, 1582 ... okay so far Wednesday October 3, 1582 ... so what's the problem? Thursday October 4, 1582 ... this is getting boring Friday October 15, 1582 ... huh? Saturday October 16, 1582 ... we've lost ten days from the calendar!
The calendar snippet shows that there are no days between October 4 and October 15. This discontinuity is called the "cutover", and we can express this succinctly: "This is a hybrid Julian/Gregorian calendar with a cutover on October 4/15 1582." Any dates before a cutover are Julian, any dates during a cutover are nonexistent, and any dates after a cutover are Gregorian.
The cutover could be at one of several points between October 4/15 1582 (when some Catholic countries switched) and January 31/February 14 1918 (when Russia switched). But a survey of important computer programs reveals that only three cutover options are in common use today.
Occasionally you'll see proleptic anniversaries (for example the anniversary of the October Revolution is in November), but non-prolepticism is the norm (for example Columbus's discovery of America was on October 12 1492 Julian and the first official celebration of the event was on October 12 1892 Gregorian).
Now it's time for a quiz-like question. Here are three queries that involve subtracting one date from another, which we performed on an Oracle database (simplified for easy reading).
|'January 1 2000' - 'January 1 1600'||146097 days|
|'January 1 1000' - 'January 1 0600'||146100 days|
|'October 15 1582' - 'October 4 1582'||1 day|
Question: Which cutover option is Oracle using? Choose: (A) October 4/15 1582, (B) September 2/14 1752, (C) never.
We'll give the answer in just a moment, but please take a stab at the question before reading on. We'll admit that this has taken a lot of explaining, but now you know something about Oracle that you almost certainly didn't know before.
The answer for Oracle is (A). If we'd tried the same thing with IBM DB2 we'd have found that the answer is (C), and if we'd tried the same thing with Microsoft SQL Server we wouldn't have gotten an answer because Microsoft refuses to accept dates before January 1 1753.
|'January 1 0001 AD' - 'December 31 0001 BC'||367 days|
Since we know there's nothing between 1 BC and 1 AD, Oracle's answer is obviously wrong. But hold on. If we'd been using astronomical year numbers, then "January 1 + 1" would be exactly 367 days after "December 31 - 1" -- because there's a year zero, and it's a leap year (divisible by four and Julian rules apply, remember?).
After trying many date-arithmetic calculations for dates before 1 AD, we're able to state this with confidence: Oracle is using BC/AD notation but is using the astronomical convention. Presumably some Oracle programmer in the distant past lifted an astronomer's calculation algorithm and plugged it into the Oracle engine, without realizing that the negative year numbers are all exactly one year different from BC numbers. Oracle can't fix this without wrecking existing applications, although it could help by admitting there's a year zero and allowing dates in "0 AD" rather than returning an error.
There is nothing you can do about this bug. If you're handling any BC dates, all you can do is remember that "February 29 4 BC" is a valid date because when you say 4 BC in Oracle, it's really 5 BC.
Alternatively, the next time you meet someone who says 2001 was the start of the millennium, tell him or her: "You're wrong, because there was a year zero. Check it out with Oracle."
"A Julian date is the number of days since January 1, 4712 BC." -- Oracle manual
Oracle's documentation is wrong. It should read: "A Julian Day number is the number of days since January 1 4713 BC (according to the proleptic Julian calendar)." -- Oracle manual if it were correct
Julian Day numbers are a continuous count of days. January 1 4713 BC was Julian Day 0, January 2 4713 BC was Julian Day 1, ..., December 31 9999 AD will be Julian Day 5373484. The year 4713 BC was chosen by Joseph Scaliger in his 1583 book De Emendatione Temporum, as a common start point for various astronomical periods. Scaliger chose the term "Julian" because a Julian year is involved. The full correct term "Julian Day number" is what the International Astronomical Union prefers.
We devised a test to confirm Oracle's veracity. According to an astronomy program, there was a total solar eclipse in Egypt on Julian Day 1153651, which (still according to the program) was July 11 1555 BC. Now look at this Oracle query:
SELECT TO_CHAR(TO_DATE('07-11-1554 BC','MM-DD-YYYY BC'),'J') FROM DUAL ... this returns "1153651"
The result shows that Oracle's Julian Day algorithm is correct, but of course the notation is wrong -- it shouldn't be 1554 BC, it should be the year -1554. That year-0 bug again.
Now let's compare Oracle's Julian-day function with IBM's. The chart below shows some AD calendar dates and the numbers that the Oracle and DB2 Julian-day functions return.
|DATE||ORACLE'S JULIAN DAY||DB2'S JULIAN DAY||DIFFERENCE|
|February 28 1970||2440646||2440646||0|
|February 28 1970||2440646||2440646||0|
|October 16 1582||2299162||2299162||0|
|October 15 1582||2299161||2299161||0|
|October 4 1582||2299160||2299150||-10|
|October 4 1582||2299160||2299150||-10|
|October 3 1582||2299159||2299149||-10|
|February 28 1000||2086366||2086361||-5|
|February 28 600||1940266||1940264||-4|
Oracle and DB2 are compatible if one uses dates after the cutover. Otherwise the values differ. If you could enter BC dates in DB2, you'd find that the difference can become quite large. To DB2, Julian Day 0 is November 24 4714 BC (proleptic Gregorian) instead of January 1 4713 BC (proleptic Julian).
Incidentally, both DB2 and Oracle have the same maximum date: December 31 9999 AD. Early editions of the Oracle documentation say that the maximum date is December 31 4712 AD, but that's no longer true.
Oracle has two trivial bugs that can lead to a misimpression about Julian Day numbers. The first bug is that Oracle thinks January 1 4713 BC is Julian Day 1 (it is actually Julian Day 0). The second bug is that Oracle thinks February 29 4713 BC is an invalid date (it is actually valid). These bugs cancel each other out, so calculations are still correct for any dates on or after March 1 4713 BC.
George II. The British Calendar Act Of 1751.
This is the text of the British law for the 1752 Julian/Gregorian switch.
Gregory XIII. Inter Gravissimas.
This is the text of Pope Gregory's directions for the 1582 Julian/Gregorian switch. It's clear from the wording that the Pope didn't intend that the new calendar rules would be applied to dates before the October 4/15 1582 cutover.
International Astronomical Union. Information Bulletin No. 81.
This is our authority for the statement that, according to astronomers, Julian Day 0 is January 1 4713 BC.
International Organization for Standardization. ISO 9075-2 (SQL Foundation).
This is our authority for the statement that the SQL Standard requires proleptic Gregorian dating.
Ixora. Julian days and year 0 (bug).
We disagree with the explanation on this site.
Oracle Corporation. Oracle8 Server SQL Reference Release 8.0 June 1997.
This is our authority for the statement that early editions of the Oracle8 manual had December 31 4712 AD as the maximum date.
Oracle Corporation. Oracle9i SQL Reference Release 2 (9.2).
Our direct quotes from the Oracle manual are from this document.
Scaliger, Joseph. De Emendatione Temporum (1583).
We have not seen the full text, but came across this quotation from it: "Julianam vocauimus, quia ad annum Julianum accommodata ..." This shows that Joseph Scaliger named Julian Day numbers after the Julian year and not, as some would have it, after his father Julius Scaliger.
Sun Corporation. Java 2 Platform -- GregorianCalendar class.
This is our authority for the statement that, in Java, the default cutover is October 4/15 1582. The ICU 2.0 document is similar.
US Naval Observatory. Julian date converter.
This web page has a form for entering dates and getting Julian Day numbers back. It's useful for confirming Oracle's algorithm, but if you use it, remember that Oracle days start at midnight while US Naval Observatory days start at noon.
Peter Gulutzan (pgulutzan at ocelot.ca) and Trudy Pelzer (tpelzer at ocelot.ca) are the authors of SQL Performance Tuning (www.ocelot.ca/tuning.htm). Their book discusses portability and optimization issues for Oracle and seven other DBMSs.
This article originally appeared in a now-defunct Oracle FAQ: http://www.orafaq.net/papers/dates_o.doc.