Java.sql.Timestamp incorrect parsing

Question

Can someone explain why this is so? Why is there a 24-minute shift this time and how to deal with it?

Scala 2.12 and Java 8.

scala> java.sql.Timestamp.valueOf("1900-01-01 00:59:00") res22: java.sql.Timestamp = 1900-01-01 00:59:00.0 scala> java.sql.Timestamp.valueOf("1900-01-01 01:00:00") res23: java.sql.Timestamp = 1900-01-01 01:24:00.0 scala> java.sql.Timestamp.valueOf("1900-01-01 01:14:00") res24: java.sql.Timestamp = 1900-01-01 01:38:00.0 scala> java.sql.Timestamp.valueOf("1900-01-01 01:20:00") res25: java.sql.Timestamp = 1900-01-01 01:44:00.0 scala> java.sql.Timestamp.valueOf("1900-01-01 01:23:00") res26: java.sql.Timestamp = 1900-01-01 01:47:00.0 scala> java.sql.Timestamp.valueOf("1900-01-01 01:24:00") res27: java.sql.Timestamp = 1900-01-01 01:24:00.0 scala> java.sql.Timestamp.valueOf("1900-01-01 01:30:00") res28: java.sql.Timestamp = 1900-01-01 01:30:00.0 

Answer 1

Look at the time zone definition in the IANA time zone database:

 # Zone NAME GMTOFF RULES FORMAT [UNTIL] Zone Europe/Warsaw 1:24:00 - LMT 1880 1:24:00 - WMT 1915 Aug 5 # Warsaw Mean Time 1:00 C-Eur CE%sT 1918 Sep 16 3:00 2:00 Poland EE%sT 1922 Jun 1:00 Poland CE%sT 1940 Jun 23 2:00 1:00 C-Eur CE%sT 1944 Oct 1:00 Poland CE%sT 1977 1:00 W-Eur CE%sT 1988 1:00 EU CE%sT 

In 1900, Poland had a time zone offset of one hour and 24 minutes from UTC, i.e. used local average solar time. This was before the introduction of standard time zones on August 5, 1915.

You must be sending PostgreSQL a timestamp without time zone , which is interpreted in your local time zone (with an offset of 1:24).

Someone (scala?) Then converts this timestamp to a timestamp in your local time zone, but mistakenly uses an offset of one hour.

I don’t know how to fix it exactly, but either use timestamp without time zone , or fix a component that believes that the Polish time was shifted by 1 hour from UTC in 1900.

Answer 2

As far as I can tell, there are two errors here. Both are (if I'm right) in the java.util.Date class, the superclass of java.sql.Timestamp .

Firstly, in 1900 there is no time shift in Warsaw. The earliest transition my Java-8 knows is in 1915. So Warsaw was at a loss 1:24 of GMT all the time. / p>

I tried:

  TimeZone.setDefault(TimeZone.getTimeZone("Europe/Warsaw")); ZoneOffset offset0124 = ZoneOffset.ofHoursMinutes(1, 24); System.out.println("" + new Date(0, 0, 1, 0, 59) + " -> " + new Date(0, 0, 1, 0, 59).toInstant().atOffset(offset0124)); System.out.println("" + new Date(0, 0, 1, 1, 14) + " -> " + new Date(0, 0, 1, 1, 14).toInstant().atOffset(offset0124)); System.out.println("" + new Date(0, 0, 1, 1, 24) + " -> " + new Date(0, 0, 1, 1, 24).toInstant().atOffset(offset0124)); 

Fingerprints:

 Mon Jan 01 00:59:00 CET 1900 -> 1900-01-01T01:23+01:24 Mon Jan 01 01:38:00 CET 1900 -> 1900-01-01T01:38+01:24 Mon Jan 01 01:24:00 CET 1900 -> 1900-01-01T01:24+01:24 

The Timestamp.valueOf method, which is used indirectly, uses the obsolete Date constructor, so I (not the same constructor, I use one without seconds, trusting it, does not matter). I will comment on the previous three cases ago:

• 1:24 handled correctly, we get the expected time from both Date.toString() and OffsetDateTime .
• 1:14 is taken as 1:38, 24 minutes later. This seems like a mistake.
• 0:59 is perceived as 1:23, also after 24 minutes. We can see this from OffsetDateTime . The same mistake. However, Date.toString() produces 00:59 as expected. It seems to me that this is the second mistake, which somehow compensates for the first. I did not check, but I suspect that the source of this error also causes Timestamp.toString() to work incorrectly.

As a check, I calculated the difference between your Timestamp objects 0:59 and 1:24. The desired result is 25 minutes or 1,500,000 milliseconds. Code:

  System.out.println(java.sql.Timestamp.valueOf("1900-01-01 01:24:00").getTime() - java.sql.Timestamp.valueOf("1900-01-01 00:59:00").getTime()); 

Will print

 60000 

60 seconds, same as 1 minute. Therefore, although both of these timestamps are printed as we expected, there is still an error.