Re: Timestamp Conversion Woes Redux

Tom Lane wrote:
> 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).

After some thought, an explicit zone identifier would solve the
java-side problems if it also meant that the timestamptz to timestamp
cast was done using that timezone.

i.e. currently the core of the problem is this behaviour:

template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
timestamp
---------------------
2005-01-01 18:00:00
(1 row)

This is not the most obvious result until you realize that timestamptz
loses timezone info:

template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;
timestamptz
------------------------
2005-01-01 18:00:00+13
(1 row)

I'd like to see the first case above produce 15:00:00, which seems
doable if timestamptz is storing a timezone offset as well as a
milliseconds-per-epoch.

If we had that behaviour, then we just always pass down a timestamptz
parameter with an appropriate timezone, and any implicit cast to
timestamp "just works". This is similar in effect to using UNKNOWN in
some ways since with UNKNOWN, if it really is a timestamp that is
needed, the timestamp parser works on the input directly and throws away
the timezone rather than doing the timezone juggling that timestamptz ->
timestamp does:

template1=# select '2005-01-01 15:00:00 +1000'::timestamp;
timestamp
---------------------
2005-01-01 15:00:00
(1 row)

-O