Re: Timestamp problem

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/