Checking my understanding as in $subject:
A TIMESTAMP WITH TIME ZONE has had its specified time zone used at the point
of entry to convert it to Z time, and then discarded. If I have to map one
of these to a date/time-with-time-zone datatype in another language, I may
as well unconditionally map it to the indicated date/time values and
explicit time zone Z, because there is no information available to
reconstruct what the original explicit time zone was.
A TIME WITH TIME ZONE actually carries its original explicit time zone
with it, but only in the form of an offset, no named time zone or DST
indication. I can map one of those faithfully if the other language has
a time-with-zone-in-offset-form data type.
Speaking of time zones, what PLs out there have a notion of time zone
maintained by their language runtimes, and how many of those use the
PostgreSQL session_timezone to initialize that?
My understanding of what PL/Java currently does is that it makes
explicit use of session_timezone on a handful of internal code paths
where it tries to map transparently between SQL and Java values, but it
does not at present use it to set the JVM's idea of the time zone, so that
remains at whatever the OS thinks it is, and that's what'll be used in any
random Java code using Java APIs that involve the time zone.
I wonder if that is or isn't common among PLs.
-Chap