Weird timezone issues with Timestamp.valueOf(String s)

[Stephen Colebourne]

Just to note that you really need someone from the JDBC maintenance
group to comment.

However, my understanding is that Timestamp conceptually represents a
date and time **without reference to a time-zone**, exactly the same
as LocalDateTime. But the implementation is poor, because it stores it
using epoch millis relative to the local time zone.

In general, I prefer to run server-side systems only in UTC to avoid
issues like this.

Stephen


On 9 February 2018 at 17:15, Martin Buchholz <martinrb at google.com> wrote:
> [redirect to core-libs-dev]
>
> On Fri, Feb 9, 2018 at 7:08 AM, <dwfranken at gmail.com> wrote:
>
>> Not that I encourage using date/time classes from the packages of either
>> java.util or java.sql, but sometimes it happens under the hood.
>>
>>
>>
>> We found a weird issue with timestamps.
>>
>> In our (PostgreSQL) database we have a column of SQL type TIMESTAMP - no
>> timezone.
>>
>>
>>
>> When JPA fills our entity field with a java.sql.Timestamp value, it is
>> given
>> an inherent timezone of the system it's running on; in our case it was CET
>> (UTC +1).
>>
>> Now this timezone isn't immediately obvious, because if you print it to
>> system out, it seems to have the correct time. However when you convert it
>> with .toInstant() the timezone rears its ugly head.
>>
>>
>>
>> I digged deeper and found Timestamp.valueOf(String s), it does a lot of
>> magic, but in the end it calls its own deprecated constructor new
>> Timestamp(int year, int month, int date,  int hour, int minute, int second,
>> int nano).
>>
>> That constructor calls the deprecated constructor of java.util.Date(int
>> year, int month, int date,  int hour, int minute, int second) and that is
>> the one doing something with the current timezone on the system.
>>
>> The method Timestamp.valueOf(String s) itself however, is not deprecated
>> and
>> I find this odd.
>>
>> More odd is that Timestamp.valueOf(LocalDateTime dateTime) has a
>> @SuppresWarnings("deprecation") annotation.
>>
>>
>>
>> Thus I found that Timestamp.valueOf("2017-10-04 00:00:00") on a system
>> running in CET timezone yields a different result than one running in UTC
>> timezone.
>>
>> Here is some example code where I put the output of the println's in
>> comments.
>>
>>
>>
>>
>> TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("Europe/Amsterdam")));
>>
>>     Timestamp fromStringCet = Timestamp.valueOf("2017-10-04 00:00:00");
>>
>>     System.out.println(fromStringCet); // 2017-10-04 00:00:00.0
>>
>>     System.out.println(fromStringCet.toInstant()); // 2017-10-03T22:00:00Z
>>
>>
>>
>>     TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC));
>>
>>     Timestamp fromStringUtc = Timestamp.valueOf("2017-10-04 00:00:00");
>>
>>     System.out.println(fromStringUtc); // 2017-10-04 00:00:00.0
>>
>>     System.out.println(fromStringUtc.toInstant()); // 2017-10-04T00:00:00Z
>>
>>
>>
>>     System.out.println(fromStringCet.equals(fromStringUtc)); // false
>>
>>
>>
>>     LocalDateTime localDateTime = LocalDateTime.of(2017, 10, 4, 0, 0, 0);
>>
>>
>>
>>
>> TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("Europe/Amsterdam")));
>>
>>     Timestamp fromLocalDateTimeCet = Timestamp.valueOf(localDateTime);
>>
>>     System.out.println(fromLocalDateTimeCet.toInstant()); //
>> 2017-10-03T22:00:00Z
>>
>>
>>
>>     TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC));
>>
>>     Timestamp fromLocalDateTimeUtc = Timestamp.valueOf(localDateTime);
>>
>>     System.out.println(fromLocalDateTimeUtc.toInstant()); // 2017-10-04
>> 00:00:00.0
>>
>>
>>
>>     System.out.println(fromLocalDateTimeCet.equals(fromLocalDateTimeUtc));
>> // false
>>
>>
>>
>> So what to do?
>>
>>
>>
>> Some options are:
>>
>> *       Make Timestamp.valueOf(String s) deprecated?
>> *       Always use UTC when doing the implicit conversion
>>
>>
>>
>> Thoughts?
>>
>>
>>
>> Dave Franken
>>
>>
>>
>>