Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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 $$
        -- Check that empname and salary are given
        -- Remember who changed the payroll when

        NEW.active_on := CURRENT_TIMESTAMP at time zone 'UTC';
        RETURN NEW;
$$ LANGUAGE plpgsql;

    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?


		       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


pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group