Re: Behavior of "at time zone"

On Friday 29 January 2010 5:34:04 am Andrew Crouch wrote:
> Hi there,
> I'm looking to use the "at time zone" language feature, however
> the results below don't really agree with my expectations. Is this a bug
> in 8.4 or am I misinterpreting the results? If it is a bug, has it been
> fixed in the development releases?
>
>
> psql (8.4.0)
> Type "help" for help.
>
> ispdb_br=> set timezone to '-2';
> SET
> ispdb_br=> select now();
> now
> -------------------------------
> 2010-01-29 11:26:22.833697-02
> (1 row)
>
> ispdb_br=> select now() at time zone 'BRST';
> timezone
> ----------------------------
> 2010-01-29 11:26:30.521964
> (1 row)
>
> ispdb_br=> select now() at time zone '-2';
> timezone
> ----------------------------
> 2010-01-29 15:26:47.010222
> (1 row)
>
> ispdb_br=> select now() at time zone '+2';
> timezone
> ---------------------------
> 2010-01-29 11:26:49.55412
> (1 row)

From here:
http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html#DATATYPE-TIMEZONES

"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. For example, SET TIMEZONE TO FOOBAR0 will work, leaving the
system effectively using a rather peculiar abbreviation for UTC. Another issue
to keep in mind is that in POSIX time zone names, positive offsets are used for
locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601
convention that positive timezone offsets are east of Greenwich. "

>
>
> Regards,
> Andrew

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com