Issues with Julian/Gregorian transition

[katzyn]

This issue related only for old dates, so for most users it does not relevant at all.

There is some common agreement that transition from Julian to Gregorian calendar system was after the last Juilan day 1582-10-04 followed by Gregorian 1582-10-15. However, this is not true for some countries (and not only a countries, there are also some local decisions about it). Old java.util.GregorianCalendar in Java supports different transitions via setGregorianChange(). JDBC have a methods to deal with different calendar systems and H2 supports them in PreparedStatement.setDate(..., Calendar) and other methods. But not in string literals. H2 accepts, for example, 1300-02-29 that valid with such agreement and not valid in proleptic Gregorian calendar, but rejects dates from 1582-10-05 to 1582-10-14 that is not valid with such agreement, but valid in some countries and in proleptic Gregorian. So it's not possible to set them in normal way in DB console.

Modern Java time API from JSR-310 supports only proleptic Gregorian calendar for some reasons.

Also H2 database always use default transition rules in assorted date-time calculations.

I checked behaviour of some other databases.

1. Oracle silently accepts dates from Julian and Gregorian calendars. But it thinks that difference between 1300-02-28 and 1300-03-01 is 2 days and difference between 1582-10-04 and 1582-10-15 is 1 day, so Oracle uses the same common transition rules in such comptations. But at least it allows to set dates like 1582-10-05 in literals. However, in its JDBC driver conversions of dates before 1582-10-15 to java.sql types is broken.

2. SQLite accepts dates from both calendars, but this database does not care about anything.

3. MySQL, MariaDB (fork of MySQL), PostgreSQL, and MS SQL Server reject date 1300-02-29 (I check only literals), but accept dates from 1582-10-05 to 1582-10-14. Thay also return difference between 1300-02-28 and 1300-03-01 as 1 day and difference between 1582-10-04 and 1582-10-15 as 11 days. This behaviour is valid for proleptic Gregorian calendar, not for traditional one.

I think that we need a some compatibility option to use other dates Juilan/Gregorian transitions including proleptic Julian and proleptic Gregorian. It is supported by GregorianCalendar anyway. I can implement such option if there are no complaints with it.

I also think that H2 should accept dates from both Julian and Gregorian calendar systems in string literals. But I don't know if H2 should just accept them without transition check (something like Oracle), or check configured transition date for safety.

[grandinj]

What is the use case for this? Who is actually using such old dates?

[katzyn]

They used for storing dates of documents from archives, for example. Also Julian calendar is still used in some religious organizations.

Code for proleptic Gregorian calendar also needed for ODBC server, because Postgre uses proleptic Gregorian calendar in network level too. I wrote a class that can handle these calendar systems and I fixed old dates before transition for ODBC driver with it in my local copy.

Now I'm trying to find a more complete solution.

There was issue #644 about year -0509, so possibly I'm not alone. I don't need negative years, but I have some issues with not so old dates.

[grandinj]

I'm ok with this, as long as it is explicitly configured i.e. should default to off

[katzyn]

BTW, SQL Standard also has only the Gregorian calendar in its specifications. So H2's behavior seems to be incompatible with the Standard, with PostgreSQL, MySQL, SQL Server, and others, and only partially compatible with Oracle (Oracle accepts everything from Julian and Gregorian, but uses traditional mixed calendar in date-time arithmetic).