db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4582) Timestamps inserted with GMT calendar are 1 hour later when subsequently read with GMT calendar (Server Mode Only).
Date Thu, 08 Apr 2010 08:37:36 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4582?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12854866#action_12854866

Knut Anders Hatlen commented on DERBY-4582:

I think I see what's going on now. The setTimestamp() method in the
client driver, and its siblings setTime() and setDate(), use the
supplied Calendar object to determine which timezone to convert the
timestamp to before storing it. The way it does this, is by adjusting
the timestamp by the same amount of time as the timezone difference
between the supplied calendar and the default calendar.

Although this adjustment will give the desired effect most of the
time, it is broken because it actually changes the timestamp to point
to a different point in time. Therefore it will miss on the exact time
of the DST changes with a couple of hours and we see these
discrepancies. We should only use the calendar to change the
presentation of timestamp, not to change its value.

I did the following experiment with the client code:

  1) removed the code that adjusts the timestamp value

  2) passed the calendar object down to the code that writes the
     timestamp to the network stream

  3) used the calendar object to extract the timezone-adjusted date
     and time out of the timezone

Since in (3) we now have the unmodified timestamp value, there's no
skew that confuses the conversion to the requested timezone, not even
around time of the DST change.

So with these changes, the correct values were sent from the client to
the server. However, there was still one discrepancy between the
values sent from the client and what's actually stored (this is an
improvement, though, since there were six discrepancies without the

This last discrepancy was caused by the handling of the timestamp on
the server. The server does not use any Calendar object explicitly
when parsing what it gets from the client or when storing the
timestamp in the database. The default timezone will therefore be used
in the handling of the timestamp. So when the client sends the
timestamp 2010-03-14 02:01:00, which is a perfectly fine timestamp in
the GMT timezone and many other timezones, the server (or more
precisely, Timestamp.valueOf(String)) thinks the timestamp refers to a
point in time within the "lost" hour in the America/Chicago timezone,
and it silently adjusts it to 03:01:00 so that it's valid in that
timezone too.

Since we don't store the timezone together with the timestamp, it
doesn't matter much which timezone the server uses when handling it,
as long as it's consistent. However, since timezones that observe DST
lose one hour when switching to DST, calendars using that timezone
cannot reliably be used to represent times in other timezones. If the
server instead had used a timezone that doesn't observe DST, like GMT,
the timestamp wouldn't change on its way from the wire to the database
even if the timestamp on the wire is not a valid time in the local

With this in mind, I extended my experiment with the following changes
in the server code (DRDAConnThread):

  4) when reading the (timezone-less) timestamp string sent from the
     client, put the values into a Calendar object with timezone set
     to GMT, and generate the timestamp from that Calendar

  5) use setTimestamp(int,Timestamp,Calendar) instead of
     setTimestamp(int,Timestamp) when passing the value to the
     embedded driver, with the Calendar's timezone set to GMT

This took care of the last discrepancy and made the client/server
setup store the exact same values on disk as the embedded setup did.

However, there's still one error when reading the values back to the
client, as noted in a previous comment about the incorrect reading of
the values generated by the embedded test. I haven't investigated this
problem yet, but I assume it's similar to the problems described

> Timestamps inserted with GMT calendar are 1 hour later when subsequently read with GMT
calendar (Server Mode Only).
> -------------------------------------------------------------------------------------------------------------------
>                 Key: DERBY-4582
>                 URL: https://issues.apache.org/jira/browse/DERBY-4582
>             Project: Derby
>          Issue Type: Bug
>          Components: Network Client
>    Affects Versions:
>         Environment: Windows XP Professional Version 2002 Service Pack 3,  Central Standard
Time Zone (America/Chicago)
>            Reporter: Keith Kruse
>            Assignee: Knut Anders Hatlen
>         Attachments: DerbyTest.java
> This issue only appears to happen in Network Server/Client mode.  Embedded mode does
not have the issue.
> My timezone is American/Chicago.  Saving timestamps with values for the 6 hours prior
to DST start are being read back in as values 1 hour later than written.  (I believe the issue
happens on the write because values written in Network Server/Client mode and read in Embedded
mode are incorrect, while values written and read in Embedded mode are corect.)
> Values between 3/13/2010 - 20:00 CST and 3/14/2010 - 02:00 CST will return timstamps
1 hour off.  The "setTimestamp" method is being passed a GMT calendar with the timestamp:
> I have a complete test class I can attach, but here is a summary:
> private final TimeZone gmtTZ = TimeZone.getTimeZone("GMT");
> private final Calendar gmtCal = Calendar.getInstance(gmtTZ);
> ...
> String sql = "INSERT INTO app.dst_test (id, gmt_timestamp, milli_time) VALUES(?,?,?)";
> String sql2 = "SELECT * from app.dst_test where id=?";
> ...
> ps.setTimestamp(2, ts, gmtCal);
> ...
> Timestamp tsRead = rs.getTimestamp("gmt_timestamp", gmtCal);
> ...

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message