Re: Timestamp Conversion Woes Redux

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> That's correct. A Timestamp is essentially a wrapper around
> milliseconds-since-epoch.

OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE
(at the moment anyway --- there's some agitation for putting an
explicit zone identifier in too, because it looks like the SQL spec
requires that).

> Timestamps are then interpreted by feeding them through a Calendar for
> locale- and timezone-specific formatting -- e.g. you can ask a Calendar
> for the hour of a particular Timestamp, and it'll give you the
> appropriate hour based on the timezone the Calendar is for.

Right, this corresponds to the server's idea of rotating the timestamptz
value to the client's TimeZone for display.

> In other words, a timestamp-without-zone is just a
> year-month-day-hour-minute-second tuple, which could correspond to many
> instants in time depending on timezone and daylight savings changes?

Exactly. This I believe is per SQL spec.

> The thing is that there are two distinct variants of setTimestamp():

> (1) setTimestamp(index, timestamp)
> (2) setTimestamp(index, timestamp, calendar)

> (2) obviously maps to timestamp-with-zone.

Hm, that's not obvious to me at all. If the timestamp is supposed to be
unconditional absolute time, then the only sensible interpretation of (1)
is that you're setting a timestamptz, and (2) would presumably produce
a timestamp-without-tz value corresponding to the local time readout of
the Calendar.

> If we go with 1(a) then we have problems when casting to a
> timestamp-without-zone value when the JVM and server timezones do not match.
> If we go with 1(b) then we have problems when casting to a
> timestamp-with-zone value when the JVM and server timezones do not
> match, or when daylight savings means there are two possible instants in
> a particular timezone identified by the timestamp-without-zone.

No matter what we do, we will have issues if the user uses the wrong
variant of setTimestamp for a particular parameter --- the server will
add its own rotation by the TimeZone offset while converting timestamp
to timestamptz or vice versa.

You could possibly avoid that gotcha by setting the server's TimeZone
to UTC, but I got the impression you wanted to avoid doing that.

regards, tom lane