Re: ResultSet.getTimestamp(Calendar) off by one-hour - Mailing list pgsql-jdbc
From | Roland Roberts |
---|---|
Subject | Re: ResultSet.getTimestamp(Calendar) off by one-hour |
Date | |
Msg-id | 49BAAE48.8050705@astrofoto.org Whole thread Raw |
In response to | ResultSet.getTimestamp(Calendar) off by one-hour (Roland Roberts <roland@astrofoto.org>) |
Responses |
Re: ResultSet.getTimestamp(Calendar) off by one-hour
Re: ResultSet.getTimestamp(Calendar) off by one-hour |
List | pgsql-jdbc |
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
pgsql-jdbc by date: