Thread: Timestamp problem
I have this problem where ResultSet.getTimestamp() messes up the value if the underlying column is of type timestamp without time zone. I have constructed a test case. Load the attached dump into a database. It creates a table with some example values, stored both as type varchar and timestamp, for cross-checking. The run the test program Test.java on that database. I see this example output if I have TZ=CET set in the environment: Column 1 returned 15000000012 Column 2 returned as string 2007-03-25 00:30:00 Column 2 returned as timestamp 2007-03-25 00:30:00.0 Column 3 returned 25.03.2007 00:30:00 Column 1 returned 15000000013 Column 2 returned as string 2007-03-25 01:30:00 Column 2 returned as timestamp 2007-03-25 01:30:00.0 Column 3 returned 25.03.2007 01:30:00 Column 1 returned 15000000014 Column 2 returned as string 2007-03-25 02:30:00 Column 2 returned as timestamp 2007-03-25 03:30:00.0 PROBLEM: ^^^^^^^^^^ Column 3 returned 25.03.2007 02:30:00 Column 1 returned 15000000015 Column 2 returned as string 2007-03-25 03:30:00 Column 2 returned as timestamp 2007-03-25 03:30:00.0 Column 3 returned 25.03.2007 03:30:00 Column 1 returned 15000000016 Column 2 returned as string 2007-03-25 04:30:00 Column 2 returned as timestamp 2007-03-25 04:30:00.0 Column 3 returned 25.03.2007 04:30:00 Note that 2007-03-25 between 02:00 and 03:00 is the change to daylight-saving time. In a DST-aware environment, the time 02:30 does not exist. Note, however, that this application does not use time zones or time-zone aware data types at all. It merely wishes to store '2007-03-25 02:30:00' and retrieve it in identical form. I suppose what is happening internally here is that the JDBC driver converts the value back and forth between several representations and because in one of those represenations 02:30 is not valid, the value gets distorted. The rest of the test data, which I have omitted from above, tests the change from DST to normal time on 2007-10-28, which has no problems. I can reproduce this with any PostgreSQL JDBC driver in existence, but FWIW, the above output is from postgresql-8.3dev-602.jdbc4.jar, and java is java version "1.6.0" Java(TM) SE Runtime Environment (build 1.6.0-b105) Java HotSpot(TM) Client VM (build 1.6.0-b105, mixed mode, sharing) -- Peter Eisentraut http://developer.postgresql.org/~petere/
Attachment
Peter Eisentraut wrote: > Note, > however, that this application does not use time zones or time-zone aware > data types at all. It merely wishes to store '2007-03-25 02:30:00' and > retrieve it in identical form. getTimestamp() must convert the retrieved timestamp to *some* timezone since Timestamp is only meaningful in a particular timezone. If you don't pass an explicit Calendar, it uses the default JVM timezone. If you want to avoid DST and similar you should explicitly pass a Calendar object to Timestamp for a timezone that does not use daylight savings (e.g. UTC) and use the same timezone to interpret the Timestamp. The internal representation of java.sql.Timestamp (which is out our control) is seconds-since-epoch, so you simply can't represent all possible times-without-timezone if you interpret that using rules from a timezone with daylight savings. In your case there is no possible seconds-since-epoch value that will represent '2007-03-25 02:30:00' in your default timezone. -O
Oliver Jowett wrote: > Peter Eisentraut wrote: > > Note, > > however, that this application does not use time zones or time-zone aware > > data types at all. It merely wishes to store '2007-03-25 02:30:00' and > > retrieve it in identical form. > > getTimestamp() must convert the retrieved timestamp to *some* timezone > since Timestamp is only meaningful in a particular timezone. If you > don't pass an explicit Calendar, it uses the default JVM timezone. Why not use UTC instead of the default JVM time zone? Or if that is not appropriate for the timestamp *with* time zone type, shouldn't you be able to tell from the RowDescription message of the server's query response whether it is supposed to be timestamp with or without time zone? I have found it tricky to predictably control "the JVM time zone". Some of these things are run from cron jobs, for example. And I think that different types of JVMs have different methods to set time zones, too. I have seen some weird behavior in the tests leading up to this. > If > you want to avoid DST and similar you should explicitly pass a Calendar > object to Timestamp for a timezone that does not use daylight savings > (e.g. UTC) and use the same timezone to interpret the Timestamp. The problem is that it's not always my code or the client's code that is acting here. There are Java tools many layers above this that appear to think that if it's a timestamp column, it should use getTimestamp(). They are not wrong, I believe. The annoying thing is that the developers in this case explicitly chose the timestamp without time zone type to avoid time zone issues altogether. (All their internal reckoning is in UTC.) So making them pass time zone information around isn't really a good answer in any case. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Oliver Jowett wrote: >> Peter Eisentraut wrote: >>> Note, >>> however, that this application does not use time zones or time-zone aware >>> data types at all. It merely wishes to store '2007-03-25 02:30:00' and >>> retrieve it in identical form. >> getTimestamp() must convert the retrieved timestamp to *some* timezone >> since Timestamp is only meaningful in a particular timezone. If you >> don't pass an explicit Calendar, it uses the default JVM timezone. > > Why not use UTC instead of the default JVM time zone? This whole area is poorly defined (again.. sigh). We use the default JVM timezone because it seems a sensible default more than anything .. the spec is silent here. Basically we just treat getTimestamp(n) as equivalent to getTimestamp(n, new GregorianCalendar()) Having ResultSet.getTimestamp() use UTC would be somewhat surprising behaviour, I think - in general other Java APIs assume the default JVM timezone when not explicitly given a timezone. As far as I can see the only way of getting predictable, portable behaviour when using without-timezone types is to always pass a Calendar. > Or if that is not > appropriate for the timestamp *with* time zone type, shouldn't you be able to > tell from the RowDescription message of the server's query response whether > it is supposed to be timestamp with or without time zone? The with time zone case shouldn't be affected anyway, if a timezone offset is present in the value we use it and ignore any provided Calendar entirely. > I have found it tricky to predictably control "the JVM time zone". Some of > these things are run from cron jobs, for example. And I think that different > types of JVMs have different methods to set time zones, too. I have seen > some weird behavior in the tests leading up to this. Does java.util.TimeZone.setDefault() not do the trick here? > The problem is that it's not always my code or the client's code that is > acting here. There are Java tools many layers above this that appear to > think that if it's a timestamp column, it should use getTimestamp(). They > are not wrong, I believe. Well.. JDBC just doesn't define what happens when you use getTimestamp() with no Calendar on a timezone-less column as far as I can tell, so YMMV if you have code that relies on a particular behaviour. compare javadoc: > getTimestamp > > public Timestamp getTimestamp(int columnIndex) > throws SQLException > > Retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp objectin the Java programming language. > getTimestamp > > public Timestamp getTimestamp(int columnIndex, > Calendar cal) > throws SQLException > > Retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp objectin the Java programming language. This method uses the given calendar to construct an appropriate millisecond valuefor the timestamp if the underlying database does not store timezone information. The Calendar variant is well defined, but the Calendar-less variant is completely silent on what it should do about timezones if they're not present in the data :( I suspect that if we changed this to UTC, then we'd get complaints along the lines of "ResultSet.getTimestamp(n) returns the wrong value, here, I printed the Timestamp and it's wrong" (because Timestamp.toString() uses the JVM's default timezone). -O