Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux