Thread: At time zone madness!

At time zone madness!

From

"John Goss"

Date:

20 April 2006, 13:31:31

I've been trawling the net trying to find an answer to this, so sorry if this is a common problem - I have tried looking before posting!

My problem is that when trying to SELECT a timestamp using "AT TIME ZONE 'Europe/London", it's subtracting an hour instead of adding it from a column storing UTC dates.

The session time zone is set to UTC, and the field is a timestamp without time zone.

However, if I do the same thing on now() instead of the result of my query, it works!

More details below:

johngoss_cs=> set session time zone 'UTC';
SET

=> select now();
now
-------------------------------
2006-04-20 13:19:04.585175+00
(1 row)

=> select now() at time zone 'Europe/London';
timezone
----------------------------
2006-04-20 14:19:12.535543
(1 row)

At the time of running this the time was 14:10 in the UK (13:10 UTC)

=> select posttime from tbldiscussionreplies where rid = 300284;
posttime
----------------------------
2006-04-20 13:10:51.160939
(1 row)

Ok, so this is the raw posttime - which should always be UTC. Works fine.

=> select posttime at time zone 'UTC' from tbldiscussionreplies where rid = 300284;
timezone
-------------------------------
2006-04-20 13:10:51.160939+00
(1 row)

Try getting it at UTC - again fine - the session time zone is set to UTC, so it doesn't change anything.

=> select posttime at time zone 'Europe/London' from tbldiscussionreplies where rid = 300284;
timezone
-------------------------------
2006-04-20 12:10:51.160939+00
(1 row)

The problem!

For some reason this has subtracted an hour - making it two hours wrong!

The field is described as:
posttime | timestamp without time zone | not null default timezone('utc'::text, now())

Any ideas?

Thanks!

John

Re: At time zone madness!

From

Tom Lane

Date:

20 April 2006, 16:20:54

"John Goss" <mrjohngoss@gmail.com> writes:
> My problem is that when trying to SELECT a timestamp using "AT TIME ZONE
> 'Europe/London", it's subtracting an hour instead of adding it from a column
> storing UTC dates.

I think you've got a misunderstanding about the behavior of AT TIME ZONE.
It's really two distinct operations depending on whether the input is a
timestamp with or without timezone.

1) If input is timestamp with timezone: rotate the time to what it would
be locally in the specified timezone, and output that as timestamp without
timezone.

2) If input is timestamp without timezone: interpret the given time as
being local time in the specified timezone, and output as timestamp WITH
timezone.  Internally, that means rotating to UTC because timestamp with
timezone is always stored as UTC internally.

So in fact one case adds the UTC offset of the given timezone, and the
other case subtracts it.

I suspect that you should be storing your posttime column as timestamp
with timezone, not without.  In general, if a stored timestamp value is
meant to represent an absolute time instant, timestamp with tz is the
correct datatype for it.  Timestamp without tz isn't very well-defined.

If you wanted to stick with timestamp-without-tz as the column datatype,
having the convention that it's always represented in UTC, then the
correct way to derive the equivalent time in some other zone would be
SELECT posttime AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London'

The first AT TIME ZONE has the effect of specifying what the stored
timestamp really means, and the second does the rotation to London time.
        regards, tom lane