Re: timezone() with timeofday() converts the wrong direction?

On Thu, Apr 21, 2005 at 04:11:57PM -0700, Steve - DND wrote:
> >
> > Perhaps this is what you want:
> >
> > SELECT timezone('UTC', timeofday()::timestamptz);
>
> That did it. Strangely, I thought I had tried that already, but I must not
> have. My next question would be if I did:
>
> SELECT timezone('UTC', timeofday()::timestamptz):timestamptz;
>
> Why do I get the timezone value as being -07(my local offset), instead
> of -00?

Again looking at the documentation, we see that "timestamp with
time zone AT TIME ZONE zone" means "Convert UTC to local time in
given time zone" and has a return type of "timestamp without time
zone". So if we run the above command without the final cast
around 16:25 PDT / 23:25 UTC, we get this:

SELECT timezone('UTC', timeofday()::timestamptz);
timezone
----------------------------
2005-04-21 23:25:12.868212
(1 row)

This result is a "timestamp without time zone", so there's no
indication that it's UTC or PDT or anything else. Since it has no
time zone, casting it to timestamptz puts it in your local time
zone:

SELECT '2005-04-21 23:25:12.868212'::timestamptz;
timestamptz
-------------------------------
2005-04-21 23:25:12.868212-07
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/