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(at)rlenter(dot)com 6818 Madeline Court
roland(at)astrofoto(dot)org Brooklyn, NY 11220