CODEWITHSUNDEEP
javasqlpostgresqlscalatimestamp
Pavel Khamutou
Pavel Khamutou

Reputation: 111

java.sql.Timestamp wrong time parsing

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

Upvotes: 5

Views: 1838

Answers (2)

Anonymous
Anonymous

Reputation: 86203

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

60000

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.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246198

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.

Upvotes: 4

Related Questions