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
– rgettman, Commented Dec 14, 2017 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
– Jim Garrison, Commented Dec 15, 2017 at 0:52
A very similar question here, just about a different time zone: Why is subtracting these two times (in 1927) giving a strange result? — Anonymous
– Anonymous, Commented Dec 15, 2017 at 8:05
I can reproduce your output in pure Java 8. So it’s not Scala’s fault. — Anonymous
– Anonymous, Commented Dec 15, 2017 at 10:53

Laurenz Albe · Accepted Answer · 2017-12-15 11:39:48Z

4

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.

edited Dec 15, 2017 at 11:39

answered Dec 15, 2017 at 9:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anonymous Over a year ago

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].

Laurenz Albe Over a year ago

Thanks. Looks like Java is the problem then. So the only remaining option is to avoid timestamp with time zone.

Anonymous Over a year ago

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

Anonymous · Accepted Answer · 2017-12-15 11:47:39Z

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.

Collectives™ on Stack Overflow

java.sql.Timestamp wrong time parsing

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related