On Jun 30, 2010, at 12:49 , Tim Landscheidt wrote:
> Michael Glaesemann <grzm@seespotcode.net> wrote:
>
>>> IMVHO best practice is to treat TIMESTAMP WITH TIME ZONE
>>> as truly evil (TM),
>
>> Um, why?
>
> Because its in- and output depend on:
>
> - The time zone set in the server's OS configuration,
> - the time zone set in the server's PostgreSQL configura-
> tion,
> - the time zone set in the user's configuration,
> - a possible "SET SESSION" command,
> - a possible "SET LOCAL" command and
Though all of this is only if you don't include a time zone as part of the value. If you use timestamp with time zone,
itrightly expects you to pass it a time zone with the timestamp value. If you don't, the server needs to make some kind
ofassumption as to what time zone you mean. In your particular case, you could always pass +00 as the time zone offset.
> - a possible "AT TIME ZONE" construct.
AT TIME ZONE can be confusing. I agree with you that formatting of timestamp values should be done at the application
(notthe database) level.
> 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
aspart 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.
Michael Glaesemann
grzm seespotcode net