Re: timezone() with timeofday() converts the wrong direction? - Mailing list pgsql-general

On Thu, Apr 21, 2005 at 01:35:16PM -0700, Steve - DND wrote:
> These attempts were run at 4/21/2005 13:15:00 -07.
> UTC: 4/21/2005 20:15:00
>
> SELECT timeofday()::timestamp
> 04/21/2005 13:15:00
>
> SELECT timezone('UTC', now())
> 04/21/2005 20:15:00 PM
>
> SELECT timezone('UTC', timeofday()::timestamp)::timestamp without time zone
> 04/21/2005 06:15:00 <- What the heck is this?!

According to the "Date/Time Functions and Operators" documentation,
timezone(zone, timestamp) is equivalent to "timestamp AT TIME ZONE
zone", and "timestamp without time zone AT TIME ZONE zone" means
"Convert local time in given time zone to UTC" and has a return
type of "timestamp with time zone".  It therefore seems to me that
you're converting timeofday() from UTC to UTC and that the output
is converted for display to your local time zone, which you then
strip off.  See the example in the documentation:

  Examples (supposing that the local time zone is PST8PDT):

  SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
  Result: 2001-02-16 19:38:40-08

  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
  Result: 2001-02-16 18:38:40

  The first example takes a zone-less time stamp and interprets it as
  MST time (UTC-7) to produce a UTC time stamp, which is then rotated to
  PST (UTC-8) for display. The second example takes a time stamp
  specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

We could rewrite the example using your values as follows:

  SELECT TIMESTAMP '2005-04-21 13:15:00' AT TIME ZONE 'UTC';
  Result: 2005-04-21 06:15:00-07

  The first example takes a zone-less time stamp and interprets it as
  UTC time to produce a UTC time stamp, which is then rotated to PDT
  (UTC-7) for display.

Perhaps this is what you want:

  SELECT timezone('UTC', timeofday()::timestamptz);

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