Re: ResultSet.getTimestamp(Calendar) off by one-hour

Okay, postgresql appears to be using the standard TZ offset regardless
of whether or not DST is in effect on the day in question.

I'm using these versions:

    297 roland> rpm -q postgresql-server postgresql-jdbc
    postgresql-server-8.3.6-1.fc10.i386
    postgresql-jdbc-8.3.603-1.1.fc10.i386

with Sun's java 1.6.12 on Fedora Core 10.

The server and client are on the same host.  I've eliminated all the
intermediate layers  of JBoss and Hibernate and written a simple, small
program that inserts a row then pulls it back from the database.  I
explicitly set the Timstamp value in the code.  Here's the code:

    import java.sql.*;
    import java.util.Calendar;
    import java.util.TimeZone;

    public class PgTest {
        private static final TimeZone TZ_UTC = TimeZone.getTimeZone("UTC");
        private static final Calendar UTC_CALENDAR =
Calendar.getInstance(TZ_UTC);

        public PgTest() {}

        public static void main(String [] args)
            throws SQLException, ClassNotFoundException,
                   IllegalAccessException, InstantiationException {

            String database = args[0];
            String username = (args.length > 1) ? args[1] : null;
            String password = (args.length > 2) ? args[2] : null;

            Class.forName("org.postgresql.Driver").newInstance();
            String url = "jdbc:postgresql:" + database;
            Connection conn = DriverManager.getConnection(url, username,
password);
            doInsert(conn);
            conn.close();

            conn = DriverManager.getConnection(url, username, password);
            doQuery(conn);
            conn.close();
        }

        public static void doInsert(Connection conn)
            throws SQLException{
            Calendar now = Calendar.getInstance();
            now.set(Calendar.YEAR, 2009);
            now.set(Calendar.MONTH, Calendar.MARCH);
            now.set(Calendar.DATE, 13);
            now.set(Calendar.HOUR_OF_DAY, 10);
            now.set(Calendar.MINUTE, 0);
            now.set(Calendar.SECOND, 0);
            now.set(Calendar.MILLISECOND, 0);

            Timestamp ts = new Timestamp(now.getTimeInMillis());
            System.out.println("timestamp is " + ts);

            PreparedStatement insert
                = conn.prepareStatement("INSERT INTO mytable (mytime)
VALUES ( ? )");
            Calendar cal = (Calendar) UTC_CALENDAR.clone();

            insert.setTimestamp(1, ts, cal);

            insert.execute();
            conn.commit();
        }

        public static void doQuery(Connection conn)
            throws SQLException {

            PreparedStatement query
                = conn.prepareStatement("SELECT mytime FROM mytable "
                                        + " WHERE id = (SELECT MAX(id)
FROM mytable)");
            ResultSet rs = query.executeQuery();
            rs.next();
            Calendar cal = (Calendar) UTC_CALENDAR.clone();
            Timestamp ts = rs.getTimestamp(1, cal);
            System.out.println("timestamp is " + ts);
            conn.commit();

        }
    }

And here is the table definition:

    create table mytable (id serial, mytime timestamp);

And here's what I get when I run it:

    305 roland> javac PgTest.java306 roland> java -cp
.:/usr/share/java/postgresql-jdbc.jar PgTest roland roland
    timestamp is 2009-03-13 10:00:00.0
    timestamp is 2009-03-13 10:00:00.0

And here is what is in the database:

    roland=# select * from mytable;
     id |       mytime
    ----+---------------------
     12 | 2009-03-13 15:00:00
    (1 row)

My system clock on this host is set to UTC, but the location is
correctly set as America/New_York, so the date shows correctly for all
application, including CURRENT_TIMESTAMP for postgresql.  But as you can
see, it added 5 to the local time to get UTC instead of 4 as it should have.

I assume this is a bug unless someone can tell me where I've gone wrong....

roland

--
               PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                            6818 Madeline Court
roland@astrofoto.org                           Brooklyn, NY 11220