You misunderstand the TIMESTAMP WITH TIMEZONE type, it doesn't store
the timezone you gave it, it's just a point in time. Saying AT TIMEZONE
just converts it to a TIMESTAMP WITHOUT TIMEZONE with the local time it
was in the timezone you gave it. So you are complaring different
things. See:
# select now(), now() at time zone 'UTC';
now | timezone
-------------------------------+----------------------------
2004-11-01 14:07:24.563239+01 | 2004-11-01 13:07:24.563239
(1 row)
They have different hours and one has a timezone and the other doesn't.
Comparing these probably adds your local timezone to the one on the
right which means the result is false.
# select (now() at time zone 'UTC')::timestamptz;
timezone
-------------------------------
2004-11-01 13:10:01.136295+01
(1 row)
Hope this clarifies it for you...
On Mon, Nov 01, 2004 at 08:02:33PM +0700, David Garamond wrote:
> Sorry, hit Sent too early...
>
> David Garamond wrote:
> >The Postgres manual says:
> >
> >The AT TIME ZONE construct allows conversions of time stamps to
> >different time zones.
> >
> >I'd guess most people would think what's meant here is something like
> >"unit conversion", and that the timestamp value stays the same (much
> >like 2 feet becomes 24 inches when it's being "converted"). But:
> >
> ># SELECT NOW() = NOW() AT TIME ZONE 'UTC';
> > ?column?
> >----------
> > f
> >(1 row)
>
> Compare with:
>
> # select timestamptz '2004-11-01 12:00:00-05' =
> timestamptz '2004-11-01 17:00:00-00';
> ?column?
> ----------
> t
> (1 row)
>
> The question is: does AT TIME TIME ZONE already do what it's supposed to
> do currently?
>
> --
> dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.