Обсуждение: Behavior of "at time zone"
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
developmentreleases?
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)
Regards,
Andrew
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.klaver@gmail.com
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
negativeoffset. Unless I'm missing something the AT TIME ZONE construct is swapping the signs of the offset when
specifyingthe timezone numerically.
Cheers,
Andrew
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Friday, 29 January 2010 11:57 AM
To: pgsql-general@postgresql.org
Cc: Andrew Crouch
Subject: Re: [GENERAL] 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
nocheck on the reasonableness of the zone abbreviations. For example, SET TIMEZONE TO FOOBAR0 will work, leaving the
systemeffectively using a rather peculiar abbreviation for UTC. Another issue to keep in mind is that in POSIX time
zonenames, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601
conventionthat positive timezone offsets are east of Greenwich. "
>
>
> Regards,
> Andrew
--
Adrian Klaver
adrian.klaver@gmail.com
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@gmail.com