Re: Timestamp with time zone

Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:

> [...]
>> So unless you are /very/ certain that all servers, all us-
>> ers and all clients will use the same settings always and
>> forever, especially if clients exchange data outside the da-
>> tabase, I'd recommend avoiding "WITH TIME ZONE" whenever
>> possible.

> Or, ensure you're including the appropriate time zone when passing values to the server, and interpreting the time zone as part of timestamp values being returned.

> By not including the time zone in the database, you're storing a piece of knowledge about the database outside of the system: the fact that the timestamp values are all UTC. I'd rather include that as part of the value stored in the database. You're trading off interpreting time zone values for hardcoding your applications to assume everything is UTC. As long as you know you're making this trade off, that's fine. Just flat out saying timestamptz is evil and should be avoided in my opinion is a little strong.

I prepended that statement with a "IMVHO" :-). But, yes: If
the /only/ advantage in using "WITH TIME ZONE" is the bit of
information "this timestamp is in UTC", I'd rather /strong-
ly/ discourage its use considering the disadvantages at-
tached.

As you say, it's a trade-off - but if I have to choose be-
tween a nice, clean contract "all timestamps from/to/in the
database are in UTC" (and fit nicely into a time_t equiva-
lent) and ensuring that all communication with the database
includes and interprets time zone offsets and the applica-
tion doesn't lose them in between, I'd go for the simpler
approach anytime.

Tim