Timestamp confusion - Mailing list pgsql-jdbc

I'm perplexed by the behaviour of ResultSet.getTimestamp() when passed
a calendar.  If I create a table with two rows:

CREATE TABLE dtest (
    uid integer NOT NULL,
    d1 timestamp with time zone,
    d2 timestamp without time zone
);

The code belows inserts a row first with the default calendar, then
with PST
Then it pulls the two rows just inserted back out, and prints them,
first with default, then with PST all using the default postgres
timezone.  Then it attempts to change the connection's timezone via
SET TIMEZONE and prints the rows again.

The results are totally baffle me, as none of the results of the PST
seem to be "correct" to me.  Is this expected behaviour?  Is the only
way to force the PG/JDBC interface to "do the right thing".  Even when
I try to SET the timezone manually, as done in the last example, the
results are still ale nonsense?

The timestamp with time zone seems to adjust the time correctly if I
SET the timezone manually with psql then do inserts/retrievals from
there, but I can not get that to work through the JDBC interface
either.  I am curious how other Java developers support user specific
timezones in their applications with Postgres?



public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver"); //load the driver
        Connection con =
DriverManager.getConnection("jdbc:postgresql:datetest","richard","password");
        con.setAutoCommit(false);
        Statement s = con.createStatement();

        s.execute("DELETE FROM dtest");

        Calendar local = new GregorianCalendar();
        System.out.println("Default TZ: " +
TimeZone.getDefault().getDisplayName());

        Timestamp ts = new Timestamp(new Date().getTime());
        System.out.println("Application time: " + ts );

        Calendar pst = new
GregorianCalendar( TimeZone.getTimeZone("PST"));

        //now we insert two, one with default timezone, one with PST:
        PreparedStatement ps = con.prepareStatement("INSERT INTO dtest
(d1,d2) VALUES (?,?)");

        ps.setTimestamp(1,ts);
        ps.setTimestamp(2,ts);
        ps.execute();

        ps.setTimestamp(1,ts, pst);
        ps.setTimestamp(2,ts, pst);
        ps.execute();

        //now print them back out using default timezone
        ResultSet rs = s.executeQuery("SELECT * FROM dtest");
        System.out.println();
        System.out.println("Default Timezone:");
        while( rs.next() ){
            System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2) +") d2(" + rs.getTimestamp(3) +")");
        }

        rs = s.executeQuery("SELECT * FROM dtest");
        System.out.println();
        System.out.println("PST Timezone:");
        while( rs.next() ){
            System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2,pst) +") d2(" + rs.getTimestamp(3,pst) +")");
        }

        s.executeUpdate("SET TIMEZONE TO 'PST8PDT';");
        rs = s.executeQuery("SELECT * FROM dtest");
        System.out.println();
        System.out.println("PG Timezone adjust to PST8PDT, Default
Calendar:");
        while( rs.next() ){
            System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2) +") d2(" + rs.getTimestamp(3) +")");
        }

        rs = s.executeQuery("SELECT * FROM dtest");
        System.out.println();
        System.out.println("PG Timezone adjust to PST8PDT, PST
Calendar");
        while( rs.next() ){
            System.out.println( "Row(" + rs.getInt(1) + ") d1("
+rs.getTimestamp(2,pst) +") d2(" + rs.getTimestamp(3,pst) +")");
        }
    }