java.sql.Timestamp wrong time parsing

Question

Can someone explain why is it so? Why there is a 24 minutes offset for that 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

What is your locale? Going through Java 8 only, no Scala, I cannot reproduce this in my locale (en_US). — rgettman, Dec 14 '17 at 22:32
I reproduced the issue with `System.setProperty("user.timezone", "Europe/Warsaw");`... looks like a bug to me, and there have been timezone bugs in the past. Stepping through the library code it looks like there might be a bogus transition in the zoneinfo database for that period. It will take some work to verify this, and I don't have time right now, unfortunately. — Jim Garrison, Dec 15 '17 at 00:52
A very similar question here, just about a different time zone: [Why is subtracting these two times (in 1927) giving a strange result?](https://stackoverflow.com/questions/6841333/why-is-subtracting-these-two-times-in-1927-giving-a-strange-result) — Ole V.V., Dec 15 '17 at 08:05
I can reproduce your output in pure Java 8. So it’s not Scala’s fault. — Ole V.V., Dec 15 '17 at 10:53

Laurenz Albe · Answer 1 · 2017-12-15T11:39:48.583

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., they were using local mean solar time. That was before standard time zones were introduced on August 5, 1915.

It must be that you feed PostgreSQL a timestamp without time zone, which is interpreted at your local time zone (with an offset of 1:24).

Somebody (scala?) then converts this timestamp back to a timestamp in your local time zone, but erroneously uses an offset of one hour.

I don't know how exactly to fix that, but either use timestamp without time zone throughout or fix the component that thinks the Polish time was offset 1 hour from UTC in 1900.

Correct: my Java 8 doesn’t know of any transition in year 1900. The earliest one it knows is in 1915: Transition[Overlap at 1915-08-05T00:00+01:24 to +01:00]. — Ole V.V., Dec 15 '17 at 10:47
Thanks. Looks like Java is the problem then. So the only remaining option is to avoid `timestamp with time zone`. — Laurenz Albe, Dec 15 '17 at 11:39
I suppose you can use timestamp-with-timezone in your database if you can only use `java.time.Instant` for saving and retrieving (or `String` if all else fails, but you’d prefer `Instant`). — Ole V.V., Dec 15 '17 at 11:41

score 2 · Answer 2 · answered Dec 15 '17 at 11:47

As far as I can tell there are two bugs involved here. Both are (if I am correct) in the java.util.Date class, the superclass of java.sql.Timestamp.

First, there is no time offset transition in Warsaw in year 1900. The earliest transition that my Java 8 knows of is in 1915. So Warsaw was at offset 1:24 from GMT during all of the time we’re concerned with.

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));

This prints:

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 method Timestamp.valueOf method that you use indirectly uses a deprecated Date constructor, so so am I (not the exact same constructor, I am using the one without seconds, trusting it makes no difference). I will comment on the above three cases backward:

1:24 is handled correctly, we get the expected time both from Date.toString() and from the OffsetDateTime.
1:14 is perceived as 1:38, 24 minutes later. This looks like a bug to me.
0:59 is perceived as 1:23, also 24 minutes later. We can see this from the OffsetDateTime. The same bug. However, Date.toString() produces 00:59 as expected. This seems to me to be a second bug that somehow compensates for the first one. I haven’t checked, but I suspect that the source of this bug also causes Timestamp.toString() to behave incorrectly.

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

    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());

This prints

60 seconds, the same as 1 minute. So even though both of those timestamps printed the way we had expected, there still is a bug involved.

java.sql.Timestamp wrong time parsing

2 Answers2