ResultSet.getTimestamp(Calendar) off by one-hour

From: Roland Roberts <roland(at)astrofoto(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: ResultSet.getTimestamp(Calendar) off by one-hour
Date: 2009-03-13 16:45:07
Message-ID: 49BA8D93.4010901@astrofoto.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Roland Roberts 2009-03-13 19:04:40 Re: ResultSet.getTimestamp(Calendar) off by one-hour
Previous Message Peter 2009-03-13 09:27:44 Re: Totally weird behaviour in org.postgresql.Driver