java.sql.Date from millis off by 1 day for dates before 1870

If I create an SQL Date object from millis, the date is off by 1 day for dates before 1870-01-01

package org.example;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormatter;
import org.joda.time.format.ISODateTimeFormat;

import java.sql.Date;

/**
 * Hello world!
 *
 */
public class App 
{

    static final DateTimeFormatter DATE_FORMATTER = ISODateTimeFormat.date();
    public static void main( String[] args )
    {
        DateTimeZone localTimeZone = DateTimeZone.forID("Asia/Kolkata");
        long millis = DATE_FORMATTER.withZone(localTimeZone).parseMillis(String.valueOf("1854-01-01"));
        Date date = new Date(millis);
        DateTime jodaDate = new DateTime(millis,localTimeZone);
        //Date date = new Date(DATE_FORMATTER.withZone(localTimeZone).parseMillis()
        System.out.println("millis " + millis + ",date " + date + ",jodadate " + jodaDate);
    }
}

Above code gives the output

millis -3660616408000,date 1853-12-31,jodadate 1854-01-01T00:00:00.000+05:53:28

In this code I'm getting the milliseconds from epoch of a string using Joda libraries. Then creating an SQL Date object from it. But this SQL date is one day off from the initial string I parsed.

Strange thing is I don't see the SQL date being off I started with any date on or after 1870-01-01. Is there something I'm missing here?

---EDIT---

Below is an example where I don't see the date being one day off.

package org.example;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormatter;
import org.joda.time.format.ISODateTimeFormat;

import java.sql.Date;

/**
 * Hello world!
 *
 */
public class App 
{

    static final DateTimeFormatter DATE_FORMATTER = ISODateTimeFormat.date();
    public static void main( String[] args )
    {
        DateTimeZone localTimeZone = DateTimeZone.forID("Asia/Kolkata");
        long millis = DATE_FORMATTER.withZone(localTimeZone).parseMillis(String.valueOf("1870-01-01"));
        Date date = new Date(millis);
        DateTime jodaDate = new DateTime(millis,localTimeZone);
        //Date date = new Date(DATE_FORMATTER.withZone(localTimeZone).parseMillis()
        System.out.println("millis " + millis + ",date " + date + ",jodadate " + jodaDate);
    }
}

Above code gives output

millis -3155692870000,date 1870-01-01,jodadate 1870-01-01T00:00:00.000+05:21:10

Preamble

Your code as written actually would be off by one day for everything, if you run that code on any JVM whose default timezone is configured to be 'west' of Asia/Kolkata, because 'your system's local timezone' is an intrinsic part of it (because new Date(millis).toString() prints the date that those millis are in as per your local timezone). Hence, a previous answerer (who since deleted their answer) who put in the effort to fully reproduce your issue misidentified the problem - part of the 'input' is also to set the platform default tz to Asia/Kolkata, which, evidently, is what your Computer is already set to.

The explanation to this problem is very complicated and requires a bunch of background.

A deep dive on tzdata.

Here is the current tzdata section for Asia/Kolkata:

# Zone	NAME		STDOFF	RULES	FORMAT	[UNTIL]
Zone	Asia/Kolkata	5:53:28 -	LMT	1854 Jun 28 # Kolkata
			            5:53:20	-	HMT	1870	    # Howrah Mean Time?
			            5:21:10	-	MMT	1906 Jan  1 # Madras local time
			            5:30	-	IST	1941 Oct
			            5:30	1:00	+0630	1942 May 15
			            5:30	-	IST	1942 Sep
			            5:30	1:00	+0630	1945 Oct 15
			            5:30	-	IST

As you can see, 1870 is an inflection point for it: A tiny little change, adjusting the offset bij 2 minutes and 10 seconds was introduced.

That is just a small snippet of a huge boatload of text that lists every timezone and every inflection point for all of them. This is called the tzdata and is a separate project. The thing is, jodatime as a library ships with its own copy of this tzdata and uses this for all its calculations, whereas new Date().toString() needs to convert its millis too in order to determine what date to show, and it uses your OpenJDK's definitions (remember, java.util.Date is a lie - it does not represent dates at all, it represents epochmillis; its toString is merely confused for backwards compatibility reasons. There's a reason almost every method in that class is deprecated, you should not be using this class for anything).

The explanation (in overview)

If the tzdata that is baked into your joda distro is different from the tzdata baked into your OpenJDK specifically in regards to Asia/Kolkata for the relevant date, we have our explanation. Jodatime needs to determine the correct millis for 1854-01-01 00:00:00 in the Asia/Kolkata zone which it does using its tzdata. This millis is then sent to j.u.Date, and then you ask j.u.Date: Convert that back to human reckoning, in my system's default zone (Presumably, also Asia/Kolkata). That SHOULD be a lossless conversion - it should always get you right back to 1854-01-01. Unless the tzdata definitions for Asia/Kolkata of your jodatime and your OpenJDK disagree.

Looking back in history, the last version of jodatime where there is a disagreement is all the way back to 2018:

Zone	Asia/Kolkata	5:53:28 -	LMT	1880        # Kolkata
			            5:53:20	-	HMT	1941 Oct    # Howrah Mean Time?
			            6:30	-	+0630	1942 May 15
			            5:30	-	IST	1942 Sep
			            5:30	1:00	+0630	1945 Oct 15
			            5:30	-	IST

It's subtle: There is a difference in the following date ranges:

Until 1870 they are in agreement: Offse of 5:53:28.

From 1870 to 1880, the current def says the offset is 5:53:20. The 2018 version, however, says the offset is 5:53:28.

Then from 1880 to 1906 the two are in agreement again (both go with 5:53:20).

From 1906 to 1941, the current version says the offset is 5:21:10, whereas the 2018 version says the offset is 5:53:20.

Then the switchover to the 5:30:00 offset date is also not the same; from 1942 they are in agreement again until the present day.

Depending on the direction of the error you won't notice. One 'direction' of error (Where old thinks the offset is less than new) you won't notice: The translation errors by translating 1910-01-01 00:00:00 to 1910-01-01 01:00:00 (not exactly - but, with the error added to the date). toString just prints the date, so you can't tell.

Its when the old thinks the offset is more than the new that you end up translating 1870-01-01 00:00:00 to 1869-12-31 23:... - that the error is subtracted, thus resulting in a date that is 'too low'.

I'm 99.9% certain your particular version of jodatime is quite old (2018 or earlier). If you update it to the latest, this problem goes away, but, your code will just fail again if ever these definitions change again. I suggest you read on so you understand how this works, and what you can do to ensure this problem won't show up in the future.

A quick note on tzdata principles

More generally, THE JVM IS BROKEN - anytime you have ALL these things happening:

• You translate any date from human reckoning to millis and back again, one step being done on JDK version X, and another on JDK version Y, where X and Y are on different sides of 'the time when OpenJDK messed up and picked the simplified version of the post-tzdata refactor' line (which is, more or less, a point update of OpenJDK17).
• That time is before 1970.

Then you should assume that an off-by-one error is going to occur in that date. This hits a few time zones particularly hard (for example, Europe/Amsterdam. For example if you store dates in the h2 database system using JDBC's .setDate and .getDate, or even .setObject(x, LocalDate), and that date is prior to 1945, it will be off by one when you upgrade your JDK.

This is a bug, I think the OpenJDK is the primary actor that messed this up, I reported it, they do not understand. I rate the odds they ever fix it as low.

NB: The reason 1970 is magic, is that the tzdata refactored: They realized that exact knowledge about timezone status prior to 1970 is incredibly hard to verify, so recently they changed policy: Any defs that affect 1970 or after are aggressively verified, fact-checked, and distributed, and an extremely high bar is required if you want to tell the tzdata maintainer that it has an error in it that affects 1970 or later. However, in contrast, a few newspaper articles are enough for pre-1970 updates and the tzdata makes far fewer guarantees about accuracy because being accurate is difficult as a general principle due to lots of details being lost to the mists of time. As part of this policy update, there are 'simple' and 'complex' versions of tzdata now: The simple version more aggressively errors for pre-1970. For example, it says that The Netherlands was like Belgium pre-1970 which is known-wrong for the second world war years. Nevertheless, tzdata will not fix it because of policy ('we do not guarantee correctness for pre-1970 if you take the simple set'), and don't want to hear that this was the wrong choice (NL is larger than Belgium, so NL should be 'leading'. Or just include both, you don't guarantee correctness but you don't guarantee wrongness either). However, the worst offender here is team OpenJDK that messed up by picking the simple set.

How to avoid this problem from hurting you

joda-time is obsolete and has been for a decade. Do not use it - java itself has a variant of joda-time now (it was added to the JDK by the author of JodaTime!), in the java.time package. In your scenario the root cause is that you are operating with different tzdata defs and all sorts of heck is going to break loose when you do that. At least by using java.time, all is in agreement.

But that's not enough - I do that and the Europe/Amsterdam thing caused a lot of pain.

You also want to avoid millis to human to millis conversion as much as you can. In order to do that properly, __do not ever use java.util.Date, and as a corrollary, do not use java.sql.Date or java.sql.Timestamp either. These things are just broken. Use .getObject(colIdx, OffsetDateTime.class) for example. It's wordy, but JDBC5 spec requires that databases support it.

Then, use a database that doesn't implement it badly. H2, unfortunately, messed this up. Understandable, perhaps - this stuff is hard to find.

OMG I didn't do that and now all my data is corrupt.

You can load the proper version of a tzdata def straight into the JDK, using the tzupdater.jar tool. If this has caused heck on your systems, find out which tzdata version was present when you did step 1 of all your conversions, load that into your JDK using tzupdater, and now the reverse of step 1 no longer causes off-by-1 errors.