Timestamp Conversion Woes Redux - Mailing list pgsql-jdbc

Hi Dave (& others),

You said,
> The problem isn't with PreparedStatement, rather with Timestamp itself.
> I ran the following
>     Timestamp ts = new Timestamp(1112511962000L);
>     System.out.println(ts);
> it prints out
>     2005-04-03 03:06:02.0

Actually, I still think it is a problem w/ PreparedStatement, and I'll
see if I can explain why, as well as provide a better test case to
illustrate.

First, the reason you got what you did up above is because you are
running in a different timezone than I am. If you were to run your
example where I live (MST), you'd get this:

    2005-04-03 00:06:02.0 (MST)
    2005-04-03 00:06:02.0 (MST - w/ daylight savings turned off)

See how those values are both the same? Now watch what happens if we
were to run the exact same piece of code in EST:

    2005-04-03 03:06:02.0 (EST)
    2005-04-03 02:06:02.0 (EST - w/ daylight savings turned off)

See how they are different? What is happening here is that 2:06 is the
actual time (EST is 2 hrs before MST, right?). But on April 3, 2005,
2:06 is not a valid time - because that is right in between when the
time is supposed to be springing forward for daylight savings.

With me so far? My point here is just that the single millisecond
value we are looking at (1112511962000L) can actually be represented
as two different things, depending on your relation to the timezone.

So let's say I read 2005-04-03 02:06:02.0 in from a db - that
timestamp will convert to the millisecond value given above, but then
when we write it back out its going to get rolled forward an hour
because of daylight savings. See the problem? The write just applied
timezone rules to my data and modified it whether I wanted it to or
not.

Now, this is only happening with prepared statements, not w/ regular
statements, and the attached example (below) illustrates that clearly.

Let's switch back to my original example, in MST. There were 3
timestamp strings:
    t1: 2005-04-03 00:06:02.000 - before the DST cutoff
    t2: 2005-04-03 02:29:43.000 - during the DST rollover
    t3: 2005-04-03 03:02:09.000 - after the DST rollover

The point here is that if we are applying timezone rules and DST is
turned on, that second value t2 is actually not valid - it's right in
the middle of the switch. So a timestamp would format it as 03:29:43.
Which is fine - except for the fact that I am reading the date from a
"timestamp without timezone" column.

Now, if I run my example code (see below) with usepstmt = false, the
code uses regular statements and I get the following results:

(usepstmt = false)
------------------
current tz: java.util.SimpleTimeZone[id=MST,offset=-25200000,dstSavings=3600000,
    useDaylight=false,...<snipped>]

starting t1: 2005-04-03 00:06:02.000
starting t2: 2005-04-03 02:29:43.000
starting t3: 2005-04-03 03:02:09.000

inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)

resulting t1: [UID]:112 [TrxTime]:2005-04-03 00:06:02.0 (millis: 1112511962000)
resulting t2: [UID]:113 [TrxTime]:2005-04-03 02:29:43.0 (millis: 1112520583000)
resulting t3: [UID]:114 [TrxTime]:2005-04-03 03:02:09.0 (millis: 1112522529000)

See how all 3 of the values stay the same? JDBC inserted all 3 dates
exactly as we requested. Now, watch what happens when I use prepared
statements instead:

(usepstmt = true)
------------------
current tz: java.util.SimpleTimeZone[id=MST,offset=-25200000,dstSavings=3600000,
    useDaylight=false,...<snipped>]

starting t1: 2005-04-03 00:06:02.000
starting t2: 2005-04-03 02:29:43.000
starting t3: 2005-04-03 03:02:09.000

inserting t1: 2005-04-03 00:06:02.0 (millis: 1112511962000)
inserting t2: 2005-04-03 02:29:43.0 (millis: 1112520583000)
inserting t3: 2005-04-03 03:02:09.0 (millis: 1112522529000)

resulting t1: [UID]:115 [TrxTime]:2005-04-03 00:06:02.0 (millis: 1112511962000)
resulting t2: [UID]:116 [TrxTime]:2005-04-03 03:29:43.0 (millis: 1112524183000)
resulting t3: [UID]:117 [TrxTime]:2005-04-03 04:02:09.0 (millis: 1112526129000)

See what happened? t2 and t3 got bumped forward an hour in the db. So
even though we said "insert 02:29:43" something in the prepared
statement applied some kind of daylight savings logic and rolled those
last two dates forward.

And THAT is where the problem lies. PreparedStatement needs to handle
these dates the same way that regular Statement does.

Does that help explain the issue any better? Please holler if you need
any further information. I am proceeding to try and poke around in the
jdbc source, but I'm not really sure where I should be looking.

This is a HUGE issue for us as we are trying to switch from MS SQL to
Postgres - we have to find a way to fix this issue asap. So if anyone
has any ideas, I am all ears.

Thanks!
Christian
-------------------------------------------
Sample Table...
-------------------------------------------

CREATE TABLE Foo (
   UID            SERIAL,
   TrxTime        timestamp without time zone NOT NULL
   , PRIMARY KEY (UID)
);

-------------------------------------------
Sample Code...
-------------------------------------------
    //set the timezone to MST so that others can easily replicate, then
    //install a variant where daylight savings is turned off (this
will allow us to
    //see the source dates un-munged, which is important here)
    TimeZone.setDefault(TimeZone.getTimeZone("MST"));
    TimeZone curTz = TimeZone.getDefault();
    TimeZone.setDefault(new SimpleTimeZone(curTz.getRawOffset(),
curTz.getID()));   //if you don't do this, t1 and t2 will get rolled
forward because of DST...
    System.out.println("current tz:"+TimeZone.getDefault());

    //now we're going to write some sample data
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    System.out.println("starting t1: "+sdf.format(new
Timestamp(1112511962000L)));     //2005-04-03 00:06:02
    System.out.println("starting t2: "+sdf.format(new
Timestamp(1112520583000L)));     //2005-04-03 02:29:43
    System.out.println("starting t3: "+sdf.format(new
Timestamp(1112522529000L)));     //2005-04-03 03:02:09

    //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 (?)");

        //clean up the table
        stmt.execute("DELETE FROM Foo");

        //insert some sample data
        //...2005-04-03 00:06:02 (before the DST cutover)
        t = new Timestamp(1112511962000L);
        System.out.println("inserting t1: "+t+" (millis: "+t.getTime()+")");
        if (usepstmt) {
            pstmt.setTimestamp(1, t);
            pstmt.executeUpdate();
        } else {
            stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
('"+sdf.format(t)+"')");
        }

        //...2005-04-03 02:29:43 (during the DST cutover)
        t = new Timestamp(1112520583000L);
        System.out.println("inserting t2: "+t+" (millis: "+t.getTime()+")");
        if (usepstmt) {
            pstmt.setTimestamp(1, t);
            pstmt.executeUpdate();
        } else {
            stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
('"+sdf.format(t)+"')");
        }

        //...2005-04-03 03:02:09 (after the DST cutover)
        t = new Timestamp(1112522529000L);
        System.out.println("inserting t3: "+t+" (millis: "+t.getTime()+")");
        if (usepstmt) {
            pstmt.setTimestamp(1, t);
            pstmt.executeUpdate();
        } else {
            stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES
('"+sdf.format(t)+"')");
        }

        if (!conn.getAutoCommit()) conn.commit();

        //now read the values back out
        ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
        int cntr = 0;
        while (rs.next()) {
            t = rs.getTimestamp(2);
            System.out.println("resulting t"+(++cntr)+":
[UID]:"+rs.getObject(1)+" [TrxTime]:"+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) {}
    }


: