Re: Timestamp with time zone

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Tim Landscheidt <tim(at)tim-landscheidt(dot)de>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Timestamp with time zone
Date: 2010-06-30 17:42:52
Message-ID: 2A98E0FE-9139-43DD-AB10-DFADC9076B41@seespotcode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


On Jun 30, 2010, at 12:49 , Tim Landscheidt wrote:

> Michael Glaesemann <grzm(at)seespotcode(dot)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, it rightly expects you to pass it a time zone with the timestamp value. If you don't, the server needs to make some kind of assumption 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 (not the 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 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.

Michael Glaesemann
grzm seespotcode net

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tim Landscheidt 2010-06-30 18:29:14 Re: Timestamp with time zone
Previous Message Tim Landscheidt 2010-06-30 16:59:53 Re: Timestamp with time zone