"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Could someone confirm that a timestamp column in a PostgreSQL database
> never contains time zone data, regardless of whether or not the "with
> time zone" clause was used in its definition. It seems to me that, if
> you ignore internals of how the data is stored (which an application
> programmer never need see), the only difference between "timestamp with
> time zone" and "timestamp without time zone" is whether a time zone
> specified in a literal will (a) be used to determine what moment the
> timestamp represents or (b) be ignored in favor of using the server's
> time zone to determine what moment the timestamp represents. In either
> case, the value returned when querying the column will be based on the
> server's time zone. Do I have that right?
No, not really.
Timestamp with time zone is effectively equivalent to Java's Timestamp
(as the latter was explained to me in this thread, anyway): all it
stores is an absolute time instant referenced to UTC. On input, any
time zone specified (or implied --- the default is to use the TimeZone
setting) is handled by rotating the value to UTC. On output, the server
rotates the time instant into the current TimeZone for display. So
setting TimeZone is equivalent to using a Calendar for conversion of
a Java Timestamp.
There has been talk of changing this behavior for improved SQL spec
compatibility, but that's how it is at the moment.
Timestamp without time zone is a fundamentally different animal, because
there simply is no concept of time zones at all. It will store any
y/m/d/h/m/s values that are legal per the rules of the Gregorian
calendar. Changing TimeZone does not affect either input interpretation
or display of a previously stored value, where TimeZone does affect both
input and display behavior of timestamptz.
I think it's really a mistake to consider timestamp without time zone
as representing any absolutely identifiable time instant at all.
Unfortunately, we are probably going to have to deal with complaints
from people who have used it in ways that amount to assuming it does
represent such an instant ...
regards, tom lane