Re: Timestamp Summary - Mailing list pgsql-jdbc

If this code only executes when the connection is established, then it
will break for any long-lived connection that lives across a change in
DST.

This could be fixed by moving this code from connection init to just
before we send any queries to the back end, sort of like:

if(defaultTz.useDaylightTime() != oldUseDaylightTime) {
  runDSTCorrectionCode();
}

I don't know how cheap the useDaylightTime() call is, so I'm not sure
what the performance impact of doing this on every query would be like.
If it's bad, then maybe we'd need to program in some other way to know
when DST turns on/off.

On Mon, 2005-07-25 at 10:45 -0600, Christian Cryder wrote:
> Hey folks,
>
> I just wanted to take a few moments and summarize where we are in our
> Timestamp woes issue. I'm also going to post a piece of code that
> illustrates some of the problems we've experienced, and then try and
> explain the conclusions we've arrived at. Hopefully this well help
> others in the future.
>
> First of all, the code snippet to easily duplicate the problem (see
> the bottom of this email). What this code does is create a table Foo,
> and then it inserts 4 rows using a SQL Statement. It then turns around
> and reads those values via JDBC, and then simply re-inserts the values
> back into the DB. As the example illustrates, the data written is NOT
> the same as the data read (you can see this by querying the table
> through the sql console after you've run the example).
>
> The key point to note here is that the 4 dates being inserted
> originally fall immediately around and in the daylight savings cutover
> (2-3 AM, 1st Sunday of April). So t1 is before daylight savings, t2 is
> during the rollover (and thus technically not a valid daylight savings
> time), t3 and t4 are after the rollover.
>
> With DST turned on on the client, here's the results in the table...
> ----------------------------------------------------
> 101;"2005-04-03 00:06:02"
> 102;"2005-04-03 02:29:43"
> 103;"2005-04-03 03:29:43"
> 104;"2005-04-03 04:35:17"
>
> 105;"2005-04-03 00:06:02"
> 106;"2005-04-03 03:29:43"
> 107;"2005-04-03 03:29:43"
> 108;"2005-04-03 04:35:17"
>
> This looks pretty good except that the original t2 (02:29) got munged
> to 03:29 in the DB..
>
> With DST programatically turned off on the client, the dates display
> properly in the client output (no munging), but they still get munged
> on the server side of things (because there, DST is still turned on) -
> and there, everything above the cutoff gets adjusted.
> -----------------------------------------------------
> 125;"2005-04-03 00:06:02"
> 126;"2005-04-03 02:29:43"
> 127;"2005-04-03 03:29:43"
> 128;"2005-04-03 04:35:17"
>
> 129;"2005-04-03 00:06:02"
> 130;"2005-04-03 03:29:43"
> 131;"2005-04-03 04:29:43"
> 132;"2005-04-03 05:35:17"
>
> So if we turn off DST on both the client -AND- the server (for me, the
> server part happens by issuing a "set timezone='etc/gmt+7'" before I
> use the connection), we finally get what we're looking for - write,
> read, write, w/ proper values on both display and in the db when we're
> all done.
> -----------------------------------------------------
> 133;"2005-04-03 00:06:02"
> 134;"2005-04-03 02:29:43"
> 135;"2005-04-03 03:29:43"
> 136;"2005-04-03 04:35:17"
>
> 137;"2005-04-03 00:06:02"
> 138;"2005-04-03 02:29:43"
> 139;"2005-04-03 03:29:43"
> 140;"2005-04-03 04:35:17"
>
> In other words, nothing got munged.
>
> At this point, I'd like to submit a piece of code that will ensure
> that the server is operating in the same time zone as the client
> connection, taking DST settings into consideration as well (Dave
> should understand where this would go as he was playing around with
> this type of thing when helping me debug last week):
>
>         //in order to avoid munging dates on insert, we need to set
> the server to the same timezone
>         //as the client for the duration of this connection. If
> daylight savings is turned on, this is
>         //simple: just send the current timezone; if dst is turned
> off, however, then we need to send the GMT
>         //equivalent (w/ no DST offset), which takes the form of
> etc/gmt+X (where X is the number of hours)
>         TimeZone tz = TimeZone.getDefault();
>         int roff = tz.getRawOffset();
>         if (tz.useDaylightTime()) {
>             stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
>         } else {
>             stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> "")+(roff/-3600000)+"'");
>         }
>
> This piece of code only needs to get executed when the connection is
> first opened to the DB, and from that point on it will ensure that the
> db is in functionally the same timezone as the server (taking DST
> on/off into account as well). In other words, if my client is running
> in MST w/ DST turned off, the server will be configured to etc/gmt+7,
> and any dates I insert will be left untouched.
>
> That make sense?
>
> If you'd like to see an example of the strings generated for each
> timezone, here's a snippet...
>
> String zones[] = TimeZone.getAvailableIDs();
> for (int i=0; i<zones.length; i++) {
>     TimeZone tz = TimeZone.getTimeZone(zones[i]);
>     int roff = tz.getRawOffset();
>     if (!tz.useDaylightTime()) {
>         System.out.println("server: 'etc/gmt"+(roff<=0 ? "+" :
> "")+(roff/-3600000)+"' ... "+i+": "+tz);
>     } else {
>         System.out.println("server: '"+tz.getID().toLowerCase()+"' ...
> "+i+": "+tz);
>     }
> }
>
> Ok, so here's the summary:
>
> a) w/ DST turned on on the client, the client munges t2 forward to a
> valid time and inserts into the db (so we read 02:29 and write 03:29)
>
> b) w/ DST turned off on the client, the client does not munge t2, and
> neither does the db because its been tweaked to match (so we read
> 02:29 and write 02:29)
>
> So if I want to insert dates without munging, all I have to do is turn
> DST off in the client (and the example below illustrates how to do
> this for the current timezone).
>
> I'd like to request that we apply this patch to the JDBC drivers
> (again, Dave will know where to put it). This should not break any
> existing code, and it will make it possible to ensure that client code
> can insert dates without munging, and without forcing DST to be turned
> off systemwide on the server - it basically allows us to drive the
> setting on a per connection basis.
>
> Any feedback?
>
> Thanks,
> Christian
>
> (and thanks especially to Dave for helping me debug all this stuff
> over the past 10 days)
>
> -------------------------------------------------
> Code snippet to duplicate problem
> -------------------------------------------------
>     //see what time zone we are running in (and optionally turn off DST)
>     TimeZone curTz = TimeZone.getDefault();
>     TimeZone curTzNoDST = new SimpleTimeZone(curTz.getRawOffset(),
> curTz.getID());
> //uncomment the line below to see what happens w/ DST turned off
> //    TimeZone.setDefault(curTzNoDST);   //this will turn off DST in
> the local JVM
>     System.out.println("current tz:"+TimeZone.getDefault());
>
>     //here we go...
>     Connection conn = null;
>     Statement stmt = null;
>     PreparedStatement pstmt = null;
>     Timestamp t = null;
>     Calendar cal = Calendar.getInstance();
>     boolean usepstmt = true;
>     try {
>         conn = ds.getConnection();
>         stmt = conn.createStatement();
>         pstmt = conn.prepareStatement("INSERT INTO Foo (TrxTime) VALUES (?)");
>
>         //drop, create the table
>         try {stmt.execute("TRUNCATE TABLE Foo");}
>         catch (SQLException e) {stmt.execute("CREATE TABLE Foo (UID
> SERIAL, TrxTime timestamp without time zone NOT NULL);");}
>
>         //in order to avoid munging dates on insert, we need to set
> the server to the same timezone
>         //as the client for the duration of this connection. If
> daylight savings is turned on, this is
>         //simple: just send the current timezone; if dst is turned
> off, however, then we need to send the GMT
>         //equivalent (w/ no DST offset), which takes the form of
> etc/gmt+X (where X is the number of hours)
>         TimeZone tz = TimeZone.getDefault();
>         int roff = tz.getRawOffset();
>         if (tz.useDaylightTime()) {
>             stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
>         } else {
>             stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> "")+(roff/-3600000)+"'");
>         }
>
>         //now start with raw inserts (via statement) - these will
> correspond to the 1st 3 rows of data in the db
>         System.out.println();
>         stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 00:06:02.0')");
>         stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 02:29:43.0')");
>         stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 03:29:43.0')");
>         stmt.execute("INSERT INTO Foo (TrxTime) VALUES ('2005-04-03
> 04:35:17.0')");
>         System.out.println("insert-1 t1: 2005-04-03 00:06:02.0
> (millis: "+Timestamp.valueOf("2005-04-03 00:06:02.0").getTime()+")");
>         System.out.println("insert-1 t2: 2005-04-03 02:29:43.0
> (millis: "+Timestamp.valueOf("2005-04-03 02:29:43.0").getTime()+")");
>         System.out.println("insert-1 t3: 2005-04-03 03:29:43.0
> (millis: "+Timestamp.valueOf("2005-04-03 03:29:43.0").getTime()+")");
>         System.out.println("insert-1 t4: 2005-04-03 04:35:17.0
> (millis: "+Timestamp.valueOf("2005-04-03 04:35:17.0").getTime()+")");
>
>         //now read the table, getting the data we just inserted (in
> your output, you will notice the timestamps differ)
>         System.out.println();
>         ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
>         List rows = new ArrayList();
>         int cntr = 0;
>         while (rs.next()) {
>             Object uid = rs.getObject(1);
>             t = rs.getTimestamp(2);
>             System.out.println("result-1 t"+(++cntr)+": "+t+" (millis:
> "+t.getTime()+")");
>             rows.add(new Object[] {uid, t});
>         }
>         rs.close();
>
>         //now iterate through the sample data and re-insert
>         System.out.println();
>         Iterator it = rows.iterator();
>         cntr = 0;
>         while (it.hasNext()) {
>             Object[] cols = (Object[]) it.next();
>             t = (Timestamp) cols[1];
>             pstmt.setTimestamp(1, t);
>             System.out.println("insert-2 t"+(++cntr)+": "+t+" (millis:
> "+t.getTime()+")");
>             pstmt.executeUpdate();
>         }
>
>         //now read the values back out (here we'll get all 6 values
> out; the last 3 correspond to 2nd set of inserts)
>         System.out.println();
>         rs = stmt.executeQuery("SELECT * FROM Foo");
>         cntr = 0;
>         while (rs.next()) {
>             t = rs.getTimestamp(2);
>             System.out.println("result-2 t"+(++cntr)+": "+t+" (millis:
> "+t.getTime()+")");
>         }
>         rs.close();
>
>     } catch (SQLException e) {
>         System.out.println("Unexpected SQLException: "+e);
>         e.printStackTrace();
>
>     } finally {
>         if (stmt!=null) try {stmt.close();} catch (SQLException e) {}
>         if (pstmt!=null) try {pstmt.close();} catch (SQLException e) {}
>         if (conn!=null) try {conn.close();} catch (SQLException e) {}
>     }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster