1

In Java via the Postgres JDBC driver, I'm running a simple query to select a very long ago datetime: select '0002-02-02 00:00:00'::timestamp. In my Java application a TimeStamp is being created with ResultSet.getTimestamp(idx, calendar) where calendar is a Calendar created by calling Calendar.getInstance(TimeZone.getTimeZone("UTC")) which looks something like this:

gregorian calendar settings

The Postgres driver does a bit of logic internally when getTimestamp is called where it appears to only use the timezone of the passed in calendar when creating the result Timestamp object. This timestamp appears reasonable in the debugger, and when calling getTime() on the Timestamp to get the number of milliseconds since the epoch, it returns -62101468800000:

enter image description here

However, if I use this same value in Python, I get a datetime that is two days different:

from datetime import datetime, timezone

num_millis=-62101468800000
num_secs=num_millis/1000

# Using the same # of seconds since the epoch as in Java, datetime is different by two days
dt = datetime.fromtimestamp(num_secs, tz=timezone.utc)
print("Datetime", dt)  # Datetime 0002-01-31 00:00:00+00:00
print("Num seconds", dt.timestamp(), "\n")  # Num seconds -62101468800.0 

# Using the same datetime string, seconds since epoch is different by two days worth of seconds
dt2 = datetime.fromisoformat('0002-02-02 00:00:00+00:00')
print("Datetime", dt2)  # Datetime 0002-02-02 00:00:00+00:00
print("Num seconds", dt2.timestamp())  # Num seconds -62101296000.0

Why is there a discrepancy between the Python code and Java code? (Is it possibly due to different calendar systems?) Is there a way to resolve this discrepancy?

JKillian
  • 18,061
  • 8
  • 41
  • 74
  • 3
    `LocalDateTime.of(LocalDate.of(2, 2, 2), LocalTime.of(0, 0)).toEpochSecond(ZoneOffset.ofHours(0))` returns `-62101296000`, although 48 hours is too much to be accounted for simply by the time zone. In any case, I would avoid `Calendar` and `GregorianCalendar` on general principle. – David Conrad Apr 26 '22 at 22:21
  • 1
    @DavidConrad - thanks, very interesting that your result matches Python, definitely seems to indicate that something unusual is going on with how `Timestamp`/`Calendar` are being used in the Java code. I'm not sure I can avoid them, but I'm in full agreement that the modern Java time API is always preferred. Found an interesting fact [with this tool](https://keisan.casio.com/exec/system/1227757509) that says Feb. 2nd 0002 in the Julian system is equivalent to Jan 31st 0002 in a Gregorian system, which seems in line with what's going on above potentially. – JKillian Apr 27 '22 at 02:29
  • 1
    @erickson I've updated the post with more details about how the calendar instance is being created. – JKillian Apr 27 '22 at 02:30
  • I recommend that in Java you don’t use `Timestamp` and `GregorianCalendar`. Those classes are poorly designed and long outdated. Instead use `OffsetDateTime`, `LocalDateTime` or `Instant`. They are all from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). Also java.time uses the proleptic Gregorian calendar system that Python appears to use. – Ole V.V. Apr 27 '22 at 07:00
  • Does this answer your question? [Java: How to get the timestamp of '1000-01-01 00:00:00' in UTC?](https://stackoverflow.com/questions/57992234/java-how-to-get-the-timestamp-of-1000-01-01-000000-in-utc) [Or this](https://stackoverflow.com/questions/58087907/different-dates-when-plus-days)? – Ole V.V. Apr 27 '22 at 07:02
  • 1
    For how to get an `OffsetDateTIme` or `LocalDateTime` from JDBC, see for example [Dates with no time or timezone component in Java/MySQL](https://stackoverflow.com/questions/285553/dates-with-no-time-or-timezone-component-in-java-mysql). – Ole V.V. Apr 27 '22 at 07:06
  • 1
    What’s the underlying column type in Postgres? – erickson Apr 27 '22 at 12:54
  • Also, the difference is due to use of a proleptic calendar by Python. But that could be a bug, depending on what date was stored in the database. History books don’t use proleptic dates; for a while around the cutover, some historical documents were dated with both “new” and “old style” dates. So, do you want to fix your Python or your Java? – erickson Apr 27 '22 at 13:00
  • I should have said "historical documents don't use proleptic dates." History *books* might; worse, they might be unclear about what dating system they are using. – erickson Apr 27 '22 at 14:10

2 Answers2

3

If you want the timestamp to be treated as a date on a proleptic Gregorian calendar (which is a big "if"), you can bypass the Julian date conversion that is otherwise performed using the getObject() method to return an OffsetDateTime.

OffsetDateTime odt = rs.getObject(i, OffsetDateTime.class);
/* Lossy conversion if you insist on saddling yourself with the misguided Timestamp type */
java.sql.Timestamp = Timestamp.valueOf(odt.toLocalDateTime());
/* Lossy conversion if you want to adopt a more reasonable representation */
LocalDate date = odt.toLocalDate();

This solution may be specific to the PostgreSQL JDBC Driver, but I've seen other drivers support JSR 310 temporal types via getObject(). It's likely to work with other databases, but test with each.

The discrepancy you see is due to the support for Julian dates that comes by default with Java's older date-time classes. The JSR 310 packages, alternatively, use a proleptic Gregorian calendar.

Because of various calendars in use contemporaneously, conflicting conventions about the first day of the year, and many other reasons, handling historical dates correctly depends heavily on your use cases. If you care about relative ordering and the interval between dates, storing dates according to a proleptic Gregorian calendar works very well, but that requires a thorough understanding of the source of a particular date to convert correctly. If you need to display the date as it was used originally, you should store and display a local date, or simply the original string, with no conversions on input or output. Of course, with two fields, you can support both at once.

erickson
  • 265,237
  • 58
  • 395
  • 493
  • 2
    `rs.getObject(i, OffsetDateTime.class)` works with any JDBC 4.2 compliant driver if the JDBC type is `timestamp with time zone`. – Ole V.V. Apr 27 '22 at 16:19
2

The difference is indeed related to different calendar systems, which the following code snippet illustrates:

// Default hybrid Julian/Gregorian calendar
GregorianCalendar cal = new GregorianCalendar();
cal.set(2, 1, 2, 0, 0, 0);
cal.set(Calendar.MILLISECOND, 0);
System.out.println(cal.getTimeInMillis());
// -62101468800000

// Pure proleptic Gregorian calendar
GregorianCalendar cal = new GregorianCalendar();
cal2.setGregorianChange(new Date(Long.MIN_VALUE));
cal2.set(2, 1, 2, 0, 0, 0);
cal2.set(Calendar.MILLISECOND, 0);
System.out.println(cal2.getTimeInMillis());
// -62101296000000

The default behavior of GregorianCalendar is:

a hybrid calendar that supports both the Julian and Gregorian calendar systems with the support of a single discontinuity, which corresponds by default to the Gregorian date when the Gregorian calendar was instituted (October 15, 1582 in some countries, later in others).

By using setGregorianChange we can change the system to be a pure proleptic Gregorian calendar:

To obtain a pure Gregorian calendar, set the change date to Date(Long.MIN_VALUE).

This gives the same -62101296000000 result as Python because it's the same way Python does calculations:

Like a date object, datetime assumes the current Gregorian calendar extended in both directions

Unfortunately, as you mentioned in the question, it appears that at this time the Postgres JDBC driver provides no way to adjust its default behavior as the passed in calendar to getTimestamp isn't fully used. So conversion would have to be done manually in your code afterwards if getTimestamp must be used.

JKillian
  • 18,061
  • 8
  • 41
  • 74