Re: Timestamp Summary - Mailing list pgsql-jdbc

Sorry, spoke before I finished thinking.  Ignore this as line noise.
-- Mark

On Mon, 2005-07-25 at 10:16 -0700, Mark Lewis wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend