Michael Glaesemann <grzm@seespotcode.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
zoneas 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.
Aslong as you know you're making this trade off, that's fine. Just flat out saying timestamptz is evil and should be
avoidedin 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