Timestamp Summary - Mailing list pgsql-jdbc

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) {}
    }