From: | Roland Roberts <roland(at)astrofoto(dot)org> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: ResultSet.getTimestamp(Calendar) off by one-hour |
Date: | 2009-03-13 19:04:40 |
Message-ID: | 49BAAE48.8050705@astrofoto.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)rlenter(dot)com 6818 Madeline Court
roland(at)astrofoto(dot)org Brooklyn, NY 11220
From | Date | Subject | |
---|---|---|---|
Next Message | Roland Roberts | 2009-03-13 20:14:28 | Re: ResultSet.getTimestamp(Calendar) off by one-hour |
Previous Message | Roland Roberts | 2009-03-13 16:45:07 | ResultSet.getTimestamp(Calendar) off by one-hour |