Search Postgresql Archives

Re: Behavior of "at time zone"

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

 



On Friday 29 January 2010 1:04:59 pm Andrew Crouch wrote:
> Hi Adrian,
>           Thanks for your reply.   However, I still don't fully understand
> why SET TIMEZONE TO and AT TIME ZONE behave differently.  Morever the
> /usr/share/pgsql/timezonesets/America.txt (POSIX) specifies the BRST
> timezone with a two hour negative offset. Unless I'm missing something the
> AT TIME ZONE construct is swapping the signs of the offset when specifying
> the timezone numerically.
>
> Cheers,
> Andrew
>

Per the docs 
http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

AT TIME ZONE needs either a text string representing a time zone or an INTERVAL. 
If it is text it follows the rules at the section I mentioned earlier. The 
first two rules specify either the full timezone name(America/Los_Angeles) or 
an abbreviation(PST). The third rule is POSIX style tz (PST8PDT). That is where 
this note comes into play:

"One should be wary that the POSIX-style time zone feature can lead to silently 
accepting bogus input, since there is no check on the reasonableness of the 
zone abbreviations."

The offsets you are supplying are being seen as POSIX offsets which are opposite 
the ISO style of SET TIMEZONE. So when you are doing '-2' you are moving 4 
hours the other direction. The two hours back to UTC and then 2 hours east of 
UTC. The +2 works because in POSIX notation that is the direction you want. To 
get the correct offset use the two forms I show at the bottom.

See the sequence below:

test=> set timezone to '-2';
SET
test=> SELECT now();
              now
-------------------------------
 2010-01-29 21:08:04.972345-02
(1 row)

test=> select now() at time zone 'BRST';
          timezone
----------------------------
 2010-01-29 21:08:31.620743
(1 row)

test=> select now() at time zone '-2';
          timezone
----------------------------
 2010-01-30 01:08:49.014289
(1 row)

test=> select now() at time zone '+2';
          timezone
----------------------------
 2010-01-29 21:09:24.612033
(1 row)


test=> select now() at time zone INTERVAL '-2:00';
          timezone
----------------------------
 2010-01-29 21:09:44.536458
(1 row)

test=> select now() at time zone 'BRST2';
          timezone
----------------------------
 2010-01-29 21:09:58.508653
(1 row)



-- 
Adrian Klaver
adrian.klaver@xxxxxxxxx

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[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