This year 2024 is a leap year.
SQL> SELECT LAST_DAY(TO_DATE(’01/02/2024′,’DD/MM/YYYY’)) FECHA FROM DUAL; FECHA —————————– Thursday, 29 February 2024
In fact, each year divisible by four is a leap year, but throughout history many adjustments occurred to the dates, which gave rise to various calendars. Our standard calendar, and also standard of most of the world, is the Gregorian calendar.
SQL> show parameters calendar NAME TYPE VALUE ————————— ———– —————————— nls_calendar string Gregorian
To understand how to handle historical dates in Oracle and understand the adaptation of how leap years are implemented in the database, I have created the following PL/SQL that shows for each year in history whether it was a leap year or not.
Take note because date bugs are coming, still unresolved in Oracle23c.
declare
anno_juliano varchar2(50);
fecha_juliana date;
begin
dbms_output.enable(1000000);
for x in 0 .. 2100
loop
if x<>0 then
anno_juliano:='01/02/'||x;
fecha_juliana:=last_day(to_date(anno_juliano,'DD/MM/SYYYY'));
dbms_output.put(to_char(fecha_juliana,'Day, DD MM SYYYY'));
if to_char(fecha_juliana,'DD')='28'
then
dbms_output.put_line(' – No es bisiesto');
else
dbms_output.put_line(' – Es bisiesto');
end if;
else
dbms_output.put ('–> Saltamos el año 0 – No existe.');
dbms_output.put_line('');
end if;
end loop;
end;
/
Now a little history to put this experiment in context.
Leap years were included by Julius Caesar in 46 BC. to synchronize the years with the seasons, that is, to make the years match with the complete rotation of the earth around the sun.
Before the rule of Julius Caesar, the Roman calendar was followed. This calendar counted the days from the founding of the city of Rome (“Ab Urbe condita”), the years had ten months and these had 29 days to match the reference of the lunar cycles. The years began in March and every two years it was added an extra month called “mercedonius” with the days pending adjustment to the rotation of the sun.
However, Julius Caesar knew from the astronomer Sisogenes of Alexandria that the Egyptians tried, without success, to reform their calendar two centuries earlier. They had calculated quite accurately that the solar year consisted of 365 days and 6 hours, and they wrote up these advances in a document known as the Decree of Canopus (engraved in stone, by the way, written in Egyptian and Greek, and currently displayed in the Cairo Museum, in Egypt).
With which it was clear that, to make the months coincide with the years, they had to have 365 “and a quarter” days in total.
But if the Egyptians were not clear about how to make the change, Julius Caesar did not find it easy either.
To begin with, the gap they already had with leaps was important.
Julius Caesar had to decree a year with 445 days, which was called the year of confusion (annus confusionis). This is the year 46 BC, and this was intended to compensate for the large accumulation of days pending adjustment.
Therefore, from that year 45 BC, first year of the Julian calendar, the years should begin in January, have 12 months as we know them, and every four years, February 23 would be repeated twice. “bis sextus dies ante calendas martii” (“repeated on the sixth day before the first day of the month of March”).
Despite everything, the use of the leap year was quite irregular during the first years of the Julian era. It is known that the Roman pontiffs applied leap years every three years and there are discrepancies about which years were actually treated as leap years or as common years. It was Caesar Augustus who stabilized the leap year every four years. Hence the months quintilis (fifth), sextilisse (sixth) were renamed July and August, in homage to the Caesars and the improvements they made to the calendar.
If you read wikipedia about articles of one of those days, you will read the discrepancies about if it was a normal year or a leap year, and which was the first day of the year. It’s a known difficulty about how to place historical documentations in a timeline.
Historically, it is considered that, starting in the year 4 AD, we have the leap years correctly from then to the present, but let’s see what happens during that time in the eyes of the database.
Modifying the PL/SQL a little, changing the FOR loop so that it evaluates the years since 46 BC. until 46 AD and only display leap years.
The result is as follows:
Monday , February 29 -0044 – It's a leap year Saturday , February 29 -0040 – It's a leap year Thursday , February 29 -0036 – It's a leap year Tuesday , February 29 -0032 – It's a leap year Sunday , February 29 -0028 – It's a leap year Friday , February 29 -0024 – It's a leap year Wednesday, February 29 -0020 – It's a leap year Monday , February 29 -0016 – It's a leap year Saturday , February 29 -0012 – It's a leap year Thursday , February 29 -0008 – It's a leap year Tuesday , February 29 -0004 – It's a leap year –> We skip year 0 – Does not exist. Friday , February 29 0004 – It's a leap year Wednesday, February 29 0008 – It's a leap year Monday , February 29 0012 – It's a leap year Saturday , February 29 0016 – It's a leap year Thursday , February 29 0020 – It's a leap year Tuesday , February 29 0024 – It's a leap year Sunday , February 29 0028 – It's a leap year Friday , February 29 0032 – It's a leap year Wednesday, February 29 0036 – It's a leap year Monday , February 29 0040 – It's a leap year Saturday , February 29 0044 – It's a leap year PL/SQL procedure successfully completed.
Although the output shows one leap year every four, it is quite difficult, due to a large number of historical inaccuracies, to correctly date the leap years at the beginning of the transition between the Roman and Julian calendars. Although we have correctly established leap years since the year 4AD to this day, for previous years Oracle applies a variant of the Gregorian calendar to date the Julian calendar dates named the “proleptic Gregorian calendar“.
Due to this proleptic Gregorian calendar, there is no “year of confusion” in the database, nor can we insert dates corresponding to the mismatches with the leaps until the time of Caesar Augustus.
All of this is covered in the ISO-8601:2004 standard date handling convention.
Thanks to this standard, we see leap year cycles every four years in the execution of PL/SQL until 44 BC (beginning of the Julian era).
Apparently everything was perfect.
However, we can observe an important anomaly in the treatment of leap years around year 0.
Repeating the PL/SQL execution for the years between -4 and 4, we obtain the following relationship of leap years and common years.
Tuesday , February 29 -0004 – It's a leap year Wednesday, February 28 -0003 – It is not a leap year Thursday , February 28 -0002 – It is not a leap year Friday , February 28 -0001 – It is not a leap year –> We skip year 0 – Does not exist. Monday , February 28, 0001 – It is not a leap year Tuesday , February 28, 0002 – It is not a leap year Wednesday, February 28, 0003 – It is not a leap year Friday , February 29, 0004 – It's a leap year PL/SQL procedure successfully completed.
So, according to the proleptic Gregorian calendar, there are six non-leap years in a row, since year zero does not exist.
But in both the proleptic Gregorian and Julian calendars, year -1 should be a leap year, although this would break the general rule of division by four.
What is actually happening?
In this implementation of ISO-8601:2004, Oracle adopts a mix between the proleptic Gregorian calendar and astronomical numbering. But according to reality, internally it is managing a year zero.
That year zero does not exist in historical calendars, but it does exist in the calendar used in astrophysics and astronomy.
This astronomical calendar has the name “Julian astronomical”, it handles the dates in a format called “Julian Day”, which has nothing to do with the Julian calendar of Julius Caesar. The Julian date counts the days from noon on January 1, -4713 to the present, including year zero in the calculation to handle with a “quarter day” precision the position of the earth with respect to the sun on dates before Christ, and it’s the standard year naming convention for NASA.
Thanks to this calendar, Fred Espenak, an astrophysicist at NASA, was able to predict with great precision both the eclipses and the phases of the moon produced from the years -1999 to the present, and future eclipses until the year 3000.
Oracle allows you to query the Julian date for each date from the TO_CHAR function.
Simply use the ‘J’ mask format.
SQL> select to_char(sysdate,’J’); TO_CHAR ——- 2460306 –> Days since January 1, -4713
Here we find out an important conflict.
It is necessary to maintain the precision of the astronomical Julian calendar for dates prior to year zero and, at the same time, to be able also to manage the proleptic Gregorian calendar.
So, here year zero is a problem.
Although in Oracle we cannot handle dates in Gregorian format for year zero (we get the ORA-01841 error), this year does exist in the Julian day count.
And here the bugs begin.
If we consult January 1 of year 1, it appears as Saturday, but the day before, December 31 of year 1 BC. He shows it to us as Wednesday.
How is that possible? We went from Wednesday to Saturday from one day to the next.
SQL> alter session set nls_date_format=’Day DD/MM/SYYYY –> J’; Session altered. SQL> select to_date(’01/01/0001′,’DD-MM-SYYYY’); TO_DATE(’01/01/0001′,’DD-MM-SYYYY —————————————— Saturday 01/01/ 0001 –> 1721424 SQL> select to_date(’01/01/0001′,’DD-MM-SYYYY’)-1; TO_DATE(’01/01/0001′,’DD-MM-SYYYY —————————————— Wednesday 12/31/-0001 –> 1721057
Furthermore, calculating with a SELECT the days between those two dates, day 1 of the current era and one day before, Oracle counts 367 days. That is, 1 day plus the 366 days of that zero leap year that does not exist in the proleptic Gregorian calendar, but does exist in the astronomical one.
SQL> select to_date(’01/01/0001′,’DD-MM-SYYYY’) – (to_date(’01/01/0001′,’DD-MM-SYYYY’)-1);
TO_DATE(’01/01/0001′,’DD-MM-SYYYY’)-(TO_DATE(’01/01/0001′,’DD-MM-SYYYY’)-1)
—————————————————————————
367
Therefore, we will get this logical error when Oracle tries to make that astronomical Julian year zero invisible in the management of Gregorian calendar dates.
Oracle Support warns that handling arithmetic operations that switch from years before zero to years after zero may give rise to certain logical corruptions, inaccuracies and bugs, even present in Oracle23c.
Let’s look in more detail at one of these date bugs manipulating the “leap” date of this invisible zero year.
Yes, in Oracle23c version.
February 29 of year -1 is not considered a leap year in the proleptic Gregorian calendar, as we have seen previously. If we try to view that date, we will get the error that it is not valid for the specified month.
SQL> select to_date(’02/29/-0001′,’DD/MM/SYYYY’);
select to_date(’02/29/-0001′,’DD/MM/SYYYY’)
*
ERROR at line 1:
ORA-01839: date not valid for month specified
Help: https://docs.oracle.com/error-help/db/ora-01839/
However, if we subtract 307 days from January 1, 1 AD. Oracle should place us on February 28 of the year 1 BC, since Oracle considers it a common non-leap year, but when we query this particular date, we come across a date that never existed.
SQL> select to_date(’01/01/0001′,’DD-MM-SYYYY’)-307; TO_DATE(’01/01/0001′,’ ———————- Saturday 29 Feb -0001
In some way it is a date that should exist, as I mentioned before, but even the date generated by to_date(’01/01/0001′,’DD-MM-SYYYY’)-307 is not even evaluable by TO_CHAR to display the day of the week, the full date or what day it corresponds to in Julian day format.
SQL> select to_char(to_date(’01/01/0001′,’DD-MM-SYYYY’)-307); TO_CHAR(TO_DATE(’01/01/0001′,’ —————————— 000000000, 00 0000000000 00000 SQL> select to_char(to_date(’01/01/0001′,’DD-MM-SYYYY’)-307,’J’); TO_CHAR ——- 0000000
That is, the treatment of dates from literals tells us that February 28 of the year -1 is a Friday, and that March 1 of the year -1 is a Saturday, which is correct.
The error occurs when handling operations on dates that cross that year zero, because it involves a contradictory conversion between the two calendars.
It is enough to perform the queries for those dates using simple literals, so that both for the calculation of its day of the week and its day in Julian date, both are displayed correctly.
SQL> select to_char(to_date(’02/28/-0001′,’DD/MM/SYYYY’),’Day, DD Month SYYYY ” julian day: ” J’); TO_CHAR(TO_DATE(’02/28/-0001′,’DD/MM/SYYYY’),’DAY,DDM —————————————————– Friday, February 28 -0001 Julian day: 1720751 SQL> select to_char(to_date(’01/03/-0001′,’DD/MM/SYYYY’),’Day, DD Month SYYYY ” julian day: ” J’); TO_CHAR(TO_DATE(’01/03/-0001′,’DD/MM/SYYYY’),’DAY,DDMO —————————————————— Saturday, March 1 -0001 Julian day: 1720752