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

Re: Timestamp problem

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Timestamp problem
Date: 2008-01-04 09:05:42
Message-ID: 200801041005.42682.peter_e@gmx.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
Oliver Jowett wrote:
> Peter Eisentraut wrote:
> > Note,
> > however, that this application does not use time zones or time-zone aware
> > data types at all.  It merely wishes to store '2007-03-25 02:30:00' and
> > retrieve it in identical form.
>
> getTimestamp() must convert the retrieved timestamp to *some* timezone
> since Timestamp is only meaningful in a particular timezone. If you
> don't pass an explicit Calendar, it uses the default JVM timezone.

Why not use UTC instead of the default JVM time zone?  Or if that is not 
appropriate for the timestamp *with* time zone type, shouldn't you be able to 
tell from the RowDescription message of the server's query response whether 
it is supposed to be timestamp with or without time zone?

I have found it tricky to predictably control "the JVM time zone".  Some of 
these things are run from cron jobs, for example.  And I think that different 
types of JVMs have different methods to set time zones, too.  I have seen 
some weird behavior in the tests leading up to this.

> If 
> you want to avoid DST and similar you should explicitly pass a Calendar
> object to Timestamp for a timezone that does not use daylight savings
> (e.g. UTC) and use the same timezone to interpret the Timestamp.

The problem is that it's not always my code or the client's code that is 
acting here.  There are Java tools many layers above this that appear to 
think that if it's a timestamp column, it should use getTimestamp().  They 
are not wrong, I believe.

The annoying thing is that the developers in this case explicitly chose the 
timestamp without time zone type to avoid time zone issues altogether.  (All 
their internal reckoning is in UTC.)  So making them pass time zone 
information around isn't really a good answer in any case.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2008-01-04 10:30:22
Subject: Re: Timestamp problem
Previous:From: Albe LaurenzDate: 2008-01-04 08:30:44
Subject: Re: JDBC driver class

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