Randall Nortman <postgreslists@wonderclown.com> writes:
> Anyway, afaik, Postgres does not store the "original" timezone
> anywhere, and so could not possibly retrieve it. I think the only
> physical difference between the "timestamp" and "timestamp with time
> zone" types is in the system catalog; the manual states that both of
> them store 8 bytes and have the same range.
There is no physical difference between the types: they are both 8-byte
quantities measuring seconds since the Epoch. I think we use midnight
1/1/2000 as the Epoch rather than the traditional Unix 1/1/1970 Epoch,
but otherwise it's exactly the same idea.
The logical difference between the two is that timestamp with tz assumes
that the Epoch is midnight UTC (which means that any particular stored
value represents a very definite real-world instant), while timestamp
without tz ignores the entire concept of time zones; its Epoch is
midnight in an unspecified time zone. Thus, timestamp with tz can and
does convert back and forth between UTC (for the stored values) and your
current TimeZone setting (for display). In timestamp without tz, what
you see is all there is.
Personally I would always use timestamp with tz for representing actual
time instants. Timestamp without tz has uses in some calendar
applications, but it is inherently ambiguous as a representation of a
specific instant. In particular, for the data recording application
that started this thread, it'd be a horrid idea to even think of using
timestamp without tz, specifically because it's incapable of dealing
with things like DST jumps.
regards, tom lane
