Re: AT TIME ZONE: "convert"?

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.