Thread: ResultSet.getTimestamp(Calendar) off by one-hour
I'm using Hiberate and JBoss with PostgreSQL as my backend. I have a
column which is set by a database trigger
CREATE FUNCTION security_biur_trg() RETURNS trigger AS $$
BEGIN
-- Check that empname and salary are given
-- Remember who changed the payroll when
NEW.active_on := CURRENT_TIMESTAMP at time zone 'UTC';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER security_bur BEFORE INSERT OR UPDATE
ON drpg.security FOR EACH ROW
EXECUTE PROCEDURE security_biur_trg();
I'm running this in New York, USA where the current offset is -4 hours
(daylight savings in effect). After the insert of a row, I can query
and confirm that the timestamp is +4 hours from my local time. But,
when I query to retrieve a row, I end up with a timestamp which is -1
from local. The query looks like this:
public Object nullSafeGet(ResultSet rs, String[] names, Object
owner) throws SQLException {
Calendar utcCalendar = (Calendar) UTC_CALENDAR.clone();
return rs.getTimestamp(names[0], utcCalendar);
}
where UTC_CALENDAR is intialized like so
private static final TimeZone TZ_UTC;
private static final Calendar UTC_CALENDAR;
static {
TZ_UTC = TimeZone.getTimeZone("UTC");
UTC_CALENDAR = Calendar.getInstance(TZ_UTC);
}
If I drop the Calendar from the getTimestamp, I get exactly what is in
the database, the actual insert time +4 hours, as expected. But with
the Calendar, I'm getting what I would expect during standard time, not
DST. Since the inserted date is today's date and today is DST, this is
simply wrong.
I'm trying to figure out if I'm missing something here or if this is a
real bug. Can someone comment? Is there something else I need to look at?
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
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
Roland Roberts wrote:
> Okay, postgresql appears to be using the standard TZ offset regardless
> of whether or not DST is in effect on the day in question.
Hmmm, I *can* get the correct behavior IF I assign the environment
variable TZ=America/New_York before I run the client program. But I
don't need to do that when I'm talking to Oracle. For the Oracle case,
the database is on another host, both still in US/Eastern time zone.
Are there other ways to get PostgreSQL JDBC to understand the client
timezone w/o explicitly setting TZ? I'm trying to find an idiot-proof
(well, I'll settle for an idiot-resistant) method.
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
Roland Roberts <roland@astrofoto.org> writes:
> And here is what is in the database:
> roland=# select * from mytable;
> id | mytime
> ----+---------------------
> 12 | 2009-03-13 15:00:00
> (1 row)
Apparently you're using a timestamp WITHOUT time zone column to store
the data. Not recommended if you are worried about timezone effects,
since by definition the apparent value depends on caller's timezone
context. Use timestamp WITH time zone and see if it gets better.
regards, tom lane
Tom Lane wrote:
> Apparently you're using a timestamp WITHOUT time zone column to store
> the data. Not recommended if you are worried about timezone effects,
> since by definition the apparent value depends on caller's timezone
> context. Use timestamp WITH time zone and see if it gets better.
>
Ultimately, I need something that will be (mostly) compatible across
different databases. The application will be using Hibernate and
switching datasources will be routine, especially as part of the testing
process. To that end, I'm using a user defined type with Hibernate to
intercept the database access and store and retrieve times in UTC
regardless of the local JVM time.
I can certainly try timestamp with time zone, but the plan is to deploy
with a column that does not include time zone because all times are
supposed to be stored in UTC. And clients WILL be connecting from other
time zones.
Based on the tests I just ran, it looks like the JVM doesn't understand
the local time zone. Although my host sure seems to know that the zone
is America/New_York, the JVM is telling me it is GMT-05:00. I'm not
sure why, but it doesn't really look like a postgresql problem at this
point.
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
Roland Roberts wrote: > Roland Roberts wrote: >> Okay, postgresql appears to be using the standard TZ offset >> regardless of whether or not DST is in effect on the day in question. > Hmmm, I *can* get the correct behavior IF I assign the environment > variable TZ=America/New_York before I run the client program. But I > don't need to do that when I'm talking to Oracle. For the Oracle > case, the database is on another host, both still in US/Eastern time > zone. > > Are there other ways to get PostgreSQL JDBC to understand the client > timezone w/o explicitly setting TZ? I'm trying to find an idiot-proof > (well, I'll settle for an idiot-resistant) method. Blah. It looks like this is actually a known Java bug, http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6456628 Although the bug was posted in August 2006(!) against Java 1.5, the most recent comment is from Aug 2008 still complaining that it is not fixed. I don't know how Java comes up with the GMT-05:00, but changing /etc/sysconfig/clock from ZONE="America/New York" to ZONE="America/New_York" (Java naming convention with the underscore) is sufficient to get everything to work although when I now include System.out.println("default TZ=" + TimeZone.getDefault().getDisplayName()); in my code, it prints default TZ=Eastern Standard Time which is a lie, its Eastern Daylight Time. In spite of that, the correct value is inserted into PostgreSQL. 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
Roland Roberts <roland@astrofoto.org> writes:
> I can certainly try timestamp with time zone, but the plan is to deploy
> with a column that does not include time zone because all times are
> supposed to be stored in UTC.
Hm, do you have the server's TimeZone setting set to UTC? Maybe the
JDBC driver is overriding that?
regards, tom lane