Oracle Tips and Tricks — David Fitzjarrell

August 7, 2008

What do Julius Caesar, Jacques Cassini and Pope Gregory XIII have in common? Oracle!

Filed under: dates,General — dfitzjarrell @ 14:06

“Oh, what a tangled web we weave, …”

Sir Walter Scott

Julian date numbers prior to AD 1 can be, well, a mess in Oracle. Take, for example, the stated date limit imposed by the database, which is January 1, 4712 BC. According to Oracle this is the start date for the Julian calendar … but it isn’t, as the start date is actually one year earlier.

Huh?!?!?

It’s true, and to explain this we need to delve into several calendars and conventions to see exactly how this came to be. We need to define a term, first, and that term is proleptic. This means to extend a calendar past the date of its inception to extrapolate dates (dates that didn’t exist in that calendar because it didn’t exist yet or it wasn’t stable). The year the Julian calendar became reliable is 4 AD, which is when the leap years stabilized [old Julius Caesar may have been a good ruler, but his math skills were apparently lacking]. Any ‘Julian’ date prior to 4 AD is considered a proleptic Julian date. And more on that later.

Julian Date Numbers (or Julian dates, as they are commonly considered) are based upon this proleptic calendar as they start at an epoch defined as January 1, 4713 BC, which is Julian Day Number 0 (since 4713 BC is WAY earlier than 4 AD [our stable starting point for the Julian calendar] this is in the proleptic Julian calendar and is, in fact, the considered ‘starting’ date for that calendar chosen because all three cycles of the Julian calendar [indiction {15-year cycle}, Metonic {roughly, a lunar cycle} and Solar] coincided for the first time). And that’s just fine, unless you run afoul of a couple of ‘gotchas‘. Those ‘gotchas’ are:

The Year Zero Rule

With the common BC/AD convention there is no Year 0, as 1 BC directly precedes 1 AD. Not so with an astronomical numbering convention, which designates years with a +/- scheme and includes a year 0 [this convention was first used by French astronomer Jacques Cassini in the mid-18th century]; the ‘AD’ years remain as-is, but the ‘BC’ end of the scale changes thusly:

2 BC is notated as -1
1 BC is notated as 0

So NOW there IS a year 0, but, well, Oracle doesn’t quite know what to do with it. And this is not the only complication, as

The inception of the Gregorian calendar

resulted in a loss of at least 10 days to correct for an excess of accumulated days due to the original, erroneous leap year rule [and we can’t thank Pope Gregory XIII enough for that]. Which 10+ days depends upon the date of adoption:

If the adoption date was October 15, 1582
—————-> then October 5 through 14 vanish.
If the adoption date was September 14, 1752
—————-> then September 3 through 13 disappear.

And there’s an option of no cutover/adoption date at all, in which the dates are considered either all Julian (going back to our proleptic Julian calendar) or all Gregorian (which establishes a proleptic Gregorian calendar) but preserves the ‘missing’ dates for eternity. [There are also other adoption dates for the Gregorian calendar throughout Europe; the two listed are the most ‘popular’.] Oracle chose to use October, 1582 as its cutover date. UNIX, on the other hand, uses the September, 1752 cutover.

So, why is Oracle unable to handle the year 0? It appears that somewhere in the development cycle a programmer decided to use the astronomical convention to calculate the Julian Day Numbers but use BC/AD notation without a correct ‘translation’. Thus the astronomical year -4712 (4712 years before year 0) becomes, in Oracle, 4712 BC instead of its actual proleptic Julian year of 4713 BC. So notation of all of the BC years in Oracle is off by 1, due to the Year 0 issue. The calculations, though, are correct, for any date on or after March 1, 4713 BC (-4712/03/01 using the astronomical notation). Why March 1, 4713 BC? Oracle considers January 1, 4713 BC as Julian Day Number 1 but it’s really Julian Day Number 0 (as mentioned previously). But, the calculation of the Julian Day Number for that date returns … 1:

SQL> with jdna as (
 2      select floor((14 - 1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6     select -4712 + 4800 - a y
 7     from dual, jdna
 8 ),
 9 jdnm as (
10      select 1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
      1.15

SQL>

[Maybe Julian Day 0 occurs at exactly noon on January 1, 4713 BC, meaning 12:01 PM on that same day would start Julian Day 1. Or not.] And, since Year 4713 BC would be a leap year (don’t ask me why, it just is) it would have a February 29:

SQL> with jdna as (
 2      select floor((14 - 2)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select -4712 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select 2 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 29 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
     59.75

SQL>

but Oracle doesn’t know that date exists, so the errors cancel each other by making March 1, 4713 BC Julian Day Number 60, which is correct. [Apparently Oracle is rounding the results because if we use the formula to calculate the Julian Day Number for March 1, 4713 BC we get:

SQL> with jdna as (
 2      select floor((14 - 3)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select -4712 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select 3 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
      60.4

SQL>

Round that calculation and you get 60; round the calculation for February 29, 4713 BC and you get … 60. This may be why Oracle refuses to accept
-4712/02/29 as a valid date:

SQL> select to_date('-4712/02/29','syyyy/mm/dd') from dual;
select to_date('-4712/02/29','syyyy/mm/dd') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified


SQL>

(two dates cannot possess the same Julian Day Number as they are unique).] Any Julian Day Number from March 1, 4713 BC onward is correct. To prove this let’s generate the rounded, truncated and actual Julian day numbers for the date range of 01/01/4713 BC to 03/01/4713 BC and see what ‘falls out’:

SQL> with jd as (
  2  SELECT to_number(to_char(daterange, 'MM')) Mth,
  3         to_number(to_char(daterange, 'DD')) D,
  4         to_number(to_char(daterange, 'SYYYY')) YR
  5  FROM
  6  (
  7    select to_date('01 -4712','MM SYYYY') + (level - 1) as DateRange
  8    from    dual
  9    where   (to_date('01 -4712','MM SYYYY')+level-1) <= last_day(sysdate)
 10    connect by level<=60
 11  )
 12  union
 13  select 2, 29, -4712 from dual
 14  ORDER BY 1,2,3
 15  ),
 16  jdna as (
 17     select floor((14 - mth)/12) a, mth mh, d dy, yr yy
 18     from jd
 19  ),
 20  jdny as (
 21     select yy + 4800 - a y, mh mn, dy dn, yy yn
 22     from jdna
 23  ),
 24  jdnm as (
 25     select mh + (12*a) - 3 m, mh mm, dy dm, yy ym
 26     from jdna
 27  )
 28  select  mh, dy,
 29         case when yy< 1 then abs(1 - yy) else yy end yr,
 30         case when yy < 1 then 'BC' else 'AD' end era,
 31         dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian,
 32         round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) rnd_julian,
 33         trunc(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083) trunc_julian,
 34         case when mh < 3 and yy < 1 then
 35              round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)-1
 36         else
 37              round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)
 38         end actual_julian
 39  from jdna, jdnm, jdny
 40  where jdnm.mm = jdna.mh
 41  and jdny.mn = jdna.mh
 42  and jdnm.dm = jdna.dy
 43* and jdny.dn = jdna.dy

        MO         DY         YR ER     JULIAN RND_JULIAN TRUNC_JULIAN ACTUAL_JULIAN
---------- ---------- ---------- -- ---------- ---------- ------------ -------------
         1          1       4713 BC       1.15          1            1             0
         1          2       4713 BC       2.15          2            2             1
         1          3       4713 BC       3.15          3            3             2
         1          4       4713 BC       4.15          4            4             3
         1          5       4713 BC       5.15          5            5             4
         1          6       4713 BC       6.15          6            6             5
         1          7       4713 BC       7.15          7            7             6
         1          8       4713 BC       8.15          8            8             7
         1          9       4713 BC       9.15          9            9             8
         1         10       4713 BC      10.15         10           10             9
         1         11       4713 BC      11.15         11           11            10
         1         12       4713 BC      12.15         12           12            11
         1         13       4713 BC      13.15         13           13            12
         1         14       4713 BC      14.15         14           14            13
         1         15       4713 BC      15.15         15           15            14
         1         16       4713 BC      16.15         16           16            15
         1         17       4713 BC      17.15         17           17            16
         1         18       4713 BC      18.15         18           18            17
         1         19       4713 BC      19.15         19           19            18
         1         20       4713 BC      20.15         20           20            19
         1         21       4713 BC      21.15         21           21            20
         1         22       4713 BC      22.15         22           22            21
         1         23       4713 BC      23.15         23           23            22
         1         24       4713 BC      24.15         24           24            23
         1         25       4713 BC      25.15         25           25            24
         1         26       4713 BC      26.15         26           26            25
         1         27       4713 BC      27.15         27           27            26
         1         28       4713 BC      28.15         28           28            27
         1         29       4713 BC      29.15         29           29            28
         1         30       4713 BC      30.15         30           30            29
         1         31       4713 BC      31.15         31           31            30
         2          1       4713 BC      31.75         32           31            31
         2          2       4713 BC      32.75         33           32            32
         2          3       4713 BC      33.75         34           33            33
         2          4       4713 BC      34.75         35           34            34
         2          5       4713 BC      35.75         36           35            35
         2          6       4713 BC      36.75         37           36            36
         2          7       4713 BC      37.75         38           37            37
         2          8       4713 BC      38.75         39           38            38
         2          9       4713 BC      39.75         40           39            39
         2         10       4713 BC      40.75         41           40            40
         2         11       4713 BC      41.75         42           41            41
         2         12       4713 BC      42.75         43           42            42
         2         13       4713 BC      43.75         44           43            43
         2         14       4713 BC      44.75         45           44            44
         2         15       4713 BC      45.75         46           45            45
         2         16       4713 BC      46.75         47           46            46
         2         17       4713 BC      47.75         48           47            47
         2         18       4713 BC      48.75         49           48            48
         2         19       4713 BC      49.75         50           49            49
         2         20       4713 BC      50.75         51           50            50
         2         21       4713 BC      51.75         52           51            51
         2         22       4713 BC      52.75         53           52            52
         2         23       4713 BC      53.75         54           53            53
         2         24       4713 BC      54.75         55           54            54
         2         25       4713 BC      55.75         56           55            55
         2         26       4713 BC      56.75         57           56            56
         2         27       4713 BC      57.75         58           57            57
         2         28       4713 BC      58.75         59           58            58
         2         29       4713 BC      59.75         60           59            59
         3          1       4713 BC      60.4          60           60            60


SQL>

Notice if the calculation is rounded then Februrary 29, 4713 BC has the same Julian Day Number as March 1, 4713 BC (as noted earlier); if the calculation is truncated then February 1, 4713 BC has the same Julian Day Number as January 31, 4713 BC and, well, we can’t have a calendar where February 1 doesn’t exist thus the reason [I suspect] that Oracle chose to round the value and then discard February 29, 4713 BC. But, had Oracle performed the numeric ‘gyrations’ illustrated in the query above we could have had proper Julian Day numbers and all of the actual proleptic dates of 4713 BC would exist.

[The above query works on Oracle releases 10.2.x and later due to the ‘connect by level<=60' statement, which produces a date list of 60 values used to generate the posted table. In releases 9.x that statement will parse and execute but may not provide any more than one row of output; in 8.x and earlier that statement will produce a syntax error.]

(Notice that the query uses the “&&” syntax for positional variables; this allows the variable to be set once, when the script is called, and passes the set value through the script to any other places where it is used. Using a single “&” would cause SQL*Plus to request the value for each occurrence in the statement. The up side to this is that variables need only be supplied values once during a run; the down side is the values are persistent between runs and either need to be submitted again on the command line [for a script] or need an explicit ‘undefine’ statement to clear them [as in ‘undefine 1’, for example, to ‘clear’ the value for &&1]. The double & can also be used on named variables, such as &&my_tbl; the undefine syntax works on these, as well, so that ‘undefine my_tbl’ will clear any set value supplied so subsequent runs will request a new value.)

And this gets better. Oracle may not recognize 1 BC as year 0 but the calculations do. Ask Oracle for the number of days difference between December 31, 1 BC and January 1, 1 AD:

SQL> select to_date('01/01/0001', 'mm/dd/yyyy') - to_date('12/31/0001 BC', 'mm/dd/yyyy bc') "This Should Be 1"
 2 from dual;

This Should Be 1
----------------
             367

SQL>

Now let’s ask Oracle to add 1 to the date December 31, 1 BC:

SQL> select to_date('31-DEC-0001 BC', 'DD-MON-YYYY AD')+1 from dual
 2 /

TO_DATE('31
-----------
01-JAN-0000

SQL>

So Year 0 exists (at least according to the JDN formula and an interesting date addition illustrated above) and it’s a leap year. Obviously Oracle takes the 1 BC value and simply tacks on a minus sign, making the year -1 in the astronomical convention — which is wrong as discussed previously. Were Oracle to do this correctly 1 BC would become Year 0 and the returned value for the above query would be 1:

SQL> with jdna as (
 2      select floor((14 - &&1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select &&3 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select &&1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14        case when &&3  with jdna as (
 2      select floor((14 - &&1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select &&3 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select &&1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14        case when &&3  select &after_jc - &before_jc from dual;

1721424-1721423
---------------
              1

SQL>

(Oracle recognizes this as a bug (106242) but, due to possible backwards compatibility issues, hasn’t fixed it.)

Can you fix the display issue in Oracle? Well, sort of — you’ll need to generate the date string, parse it, then, if it’s a BC date add one more to the year:

SQL> declare
 2      yr number;
 3      bc varchar2(2);
 4      mo varchar2(10);
 5      dy varchar2(2);
 6
 7 begin
 8      select to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'AD'),
 9             to_number(to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'YYYY')),
10             to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'MONTH'),
11             to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'DD')
12      into bc, yr, mo, dy
13      from dual;
14
15      if bc = 'BC' then
16           yr := yr + 1;
17      end if;
18
19      dbms_output.put_line('The date submitted is '||mo||' '||dy||', '||yr||' '||bc);
20
21 end;
22 /
The date submitted is JANUARY 01, 4713 BC

PL/SQL procedure successfully completed.

SQL>

It may be easier to simply remember the BC date notation is one year off.

But, hey, the Julian Date Numbers are correct … if you don’t count January and February of 4713 BC … so is Oracle wrong or is the formula wrong? It appears a bit of both, really, as the formula (as proven) returns the wrong values for dates from the epoch through February 29, 4713 BC, and Oracle doesn’t ‘translate’ the astronomical numbering convention correctly, associating -1 with 1 BC. When it rains, it pours.

Now, where did I put that leap year?

Advertisements

3 Comments »

  1. great article – good choice of date since Year 4713 would be a leap year

    Comment by Jamie — April 23, 2009 @ 15:49 | Reply

  2. […] you can also use Julian date format, which counts the number of days from 01-01-4712 BC to the desired date, putting these values in the 2.4 million range of […]

    Pingback by How About A Date? « Oracle Tips and Tricks — July 22, 2012 @ 22:08 | Reply

  3. 4713 BC is leap year because of the lack of the year AD 0. If AD 4 is aleap year then AD 0 = 1 BC is also a (Julian) leap year. Likewise, a Julian leap year should occur 4712 years before that.

    Comment by Swin — August 29, 2014 @ 10:35 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: