Обсуждение: BUG #5912: Etc/GMT time utc offset error

The following bug has been logged online:

Bug reference:      5912
Logged by:          Ross Williams
Email address:      ross.williams@watchguard.com
PostgreSQL version: 8.3.1 and 8.4.7
Operating system:   FreeBSD 7.3 and Ubuntu 10
Description:        Etc/GMT time utc offset error
Details:

On the target OS the offsets are correct GMT+ gives a increased utc_offset.
When I attempt to set via postgres, the timezone are flipped + for - and
vice versa.

http://wwp.greenwichmeantime.com/info/timezone.htm

Gives a clear indication that there is an error in the utc_offset below.

 name         | abbrev | utc_offset | is_dst

Etc/GMT-14    | GMT-14 | 14:00:00   | f
Etc/GMT-13    | GMT-13 | 13:00:00   | f
Etc/GMT-12    | GMT-12 | 12:00:00   | f
Etc/GMT-11    | GMT-11 | 11:00:00   | f
Etc/GMT-10    | GMT-10 | 10:00:00   | f
Etc/GMT-9     | GMT-9  | 09:00:00   | f
Etc/GMT-8     | GMT-8  | 08:00:00   | f
Etc/GMT-7     | GMT-7  | 07:00:00   | f
Etc/GMT-6     | GMT-6  | 06:00:00   | f
Etc/GMT-5     | GMT-5  | 05:00:00   | f
Etc/GMT-4     | GMT-4  | 04:00:00   | f
Etc/GMT-3     | GMT-3  | 03:00:00   | f
Etc/GMT-2     | GMT-2  | 02:00:00   | f
Etc/GMT-1     | GMT-1  | 01:00:00   | f
Etc/GMT+1     | GMT+1  | -01:00:00  | f
Etc/GMT+2     | GMT+2  | -02:00:00  | f
Etc/GMT+3     | GMT+3  | -03:00:00  | f
Etc/GMT+4     | GMT+4  | -04:00:00  | f
Etc/GMT+5     | GMT+5  | -05:00:00  | f
Etc/GMT+6     | GMT+6  | -06:00:00  | f
Etc/GMT+7     | GMT+7  | -07:00:00  | f
Etc/GMT+8     | GMT+8  | -08:00:00  | f
Etc/GMT+9     | GMT+9  | -09:00:00  | f
Etc/GMT+10    | GMT+10 | -10:00:00  | f
Etc/GMT+11    | GMT+11 | -11:00:00  | f
Etc/GMT+12    | GMT+12 | -12:00:00  | f

"Ross Williams" <ross.williams@watchguard.com> writes:
> On the target OS the offsets are correct GMT+ gives a increased utc_offset.
> When I attempt to set via postgres, the timezone are flipped + for - and
> vice versa.

The problem is that there are conflicting standards for the sign of UTC
offsets.  The conventions Postgres has chosen are not 100% consistent,
but neither is the real world.  There's some discussion in this
section of the manual:

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

in particular this bit:

    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, tom lane