Обсуждение: Timestamp with time zone

Hello,

I'm running a Debian Lenny box with PostgreSQL 8.4.4 from the backports.
I have a question regarding timestamps with time zone. I can't get time
zones inserted into my timestamp with time zone columns. For example:

> CREATE TABLE testcase(
>     created TIMESTAMP WITH TIME ZONE
> );
> INSERT INTO testcase VALUES (TIMESTAMP WITH TIME ZONE '2005-01-01 00:00+01');
> SELECT created, EXTRACT(TIMEZONE FROM created) FROM testcase;
>         created         | date_part
> ------------------------+-----------
>  2004-12-31 23:00:00+00 |         0
> (1 ligne)

Or from the docs:

> SELECT TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02';
>       timestamptz
> ------------------------
>  2004-10-19 08:23:54+00

In both examples, the timezone is lost. I read that

> To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type

but how can I do that in pactice?

Thanks,
Xavier

Xavier Robin <xavier.robin@bluewin.ch> wrote:

> [...]
> In both examples, the timezone is lost. I read that

>> To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type

> but how can I do that in pactice?

You should have read on :-):

| For timestamp with time zone, the internally stored value is
                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| always in UTC (Universal Coordinated Time, traditionally
  ^^^^^^^^^^^^^
| known as Greenwich Mean Time, GMT). An input value that has
| an explicit time zone specified is converted to UTC using
| the appropriate offset for that time zone. If no time zone
| is stated in the input string, then it is assumed to be in
| the time zone indicated by the system's timezone parameter,
| and is converted to UTC using the offset for the timezone
| zone.

| When a timestamp with time zone value is output, it is al-
                                                   ^^^^^^^^^
| ways converted from UTC to the current timezone zone, and
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| displayed as local time in that zone. To see the time in an-
| other time zone, either change timezone or use the AT TIME
| ZONE construct (see Section 9.9.3).

So if you need the timezone information, you'll have to re-
cord it separately.

  IMVHO best practice is to treat TIMESTAMP WITH TIME ZONE
as truly evil (TM), only use UTC timestamps in the database
and only convert them from and to something timezonic in
your application when it has to interact with a human being.

Tim

On Jun 30, 2010, at 10:25 , Tim Landscheidt wrote:

>  IMVHO best practice is to treat TIMESTAMP WITH TIME ZONE
> as truly evil (TM),

Um, why?

Michael Glaesemann
grzm seespotcode net

(copy to the list)
Le 30.06.2010 16:25, Tim Landscheidt a écrit :
> So if you need the timezone information, you'll have to re-
> cord it separately.

Oh, I was thinking about that, but it sounded weird because it was what
I was expecting from a TIMESTAMP WITH*OUT* TIME ZONE column.

Just one more question to make sure I got it all right:

EXTRACT(timezone FROM <a timestamptz here>)

will *always* return 0? Because the doc suggests that positive or
negative values can be returned…
And (just 1 more) so what's the point of this field in the first place?


>   IMVHO best practice is to treat TIMESTAMP WITH TIME ZONE
> as truly evil (TM), only use UTC timestamps in the database
> and only convert them from and to something timezonic in
> your application when it has to interact with a human being.

I'll try to keep that in mind!

Thanks,
Xavier

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
- a possible "AT TIME ZONE" construct.

Add to that the possibility that the zoneinfo data might
differ from server to server and over time.

  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.

Tim

Xavier Robin <xavier.robin@bluewin.ch> wrote:

>> So if you need the timezone information, you'll have to re-
>> cord it separately.

> Oh, I was thinking about that, but it sounded weird because it was what
> I was expecting from a TIMESTAMP WITH*OUT* TIME ZONE column.

> Just one more question to make sure I got it all right:

> EXTRACT(timezone FROM <a timestamptz here>)

> will *always* return 0? Because the doc suggests that positive or
> negative values can be returned…

No, it will return the offset of your current time zone:

| tim=# BEGIN WORK;
| BEGIN
| tim=# CREATE TEMPORARY TABLE TestTZ (t TIMESTAMP WITH TIME ZONE);
| CREATE TABLE
| tim=# INSERT INTO TestTZ (t) VALUES (now());
| INSERT 0 1
| tim=# SET LOCAL timezone TO ':Europe/Athens'; SELECT EXTRACT(timezone FROM t) FROM TestTZ;
| SET
|  date_part
| -----------
|      10800
| (1 Zeile)

| tim=# SET LOCAL timezone TO ':Europe/Berlin'; SELECT EXTRACT(timezone FROM t) FROM TestTZ;
| SET
|  date_part
| -----------
|       7200
| (1 Zeile)

| tim=# SET LOCAL timezone TO 'UTC'; SELECT EXTRACT(timezone FROM t) FROM TestTZ;
| SET
|  date_part
| -----------
|          0
| (1 Zeile)

| tim=#

> And (just 1 more) so what's the point of this field in the first place?
> [...]

I don't know :-).

Tim

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

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

Le 30.06.2010 18:59, Tim Landscheidt a écrit :
> Xavier Robin <xavier.robin@bluewin.ch> wrote:
>> Just one more question to make sure I got it all right:
>
>> EXTRACT(timezone FROM <a timestamptz here>)
>
>> will *always* return 0? Because the doc suggests that positive or
>> negative values can be returned…
>
> No, it will return the offset of your current time zone:

I got it, thanks a lot! :)
So WITH TIME ZONE was not at all what I expected (thought it would store
the time zone of the timestamp entered). I'll see if switching to
TIMESTAMP WITHOUT TIME ZONE is what I need now.

Thanks all for your answers and insights.

Xavier