Re: Timezone issues with Postrres - Mailing list pgsql-bugs

On 21-09-2011 13:38, Robert Haas wrote:
> On Wed, Sep 21, 2011 at 3:16 AM, pratikchirania<pratik.chirania@hp.com>  wrote:
>> The command clearly does not return '--with-system-tzdata'. I am using
>> Windows server 2008 R2. The TZ data must be working fine as other
>> applications on the OS are working fine. The issue is also reproducible on
>> Postgre version 8.3.
>
> You said that your PostgreSQL time zone was set to UTC-6.  Are you
> sure that's the case?  What's the output from 'SHOW timezone'?   Also,
> what's the system time zone set to?
>
> The reason I ask is because, for me, setting the time zone to UTC-6
> gives me a time that is six hours AHEAD of UTC, which wouldn't be
> appropriate for South America:
>
> rhaas=# select now() at time zone 'utc', now() at time zone 'utc -6';
>            timezone          |          timezone
> ----------------------------+----------------------------
>   2011-09-21 16:31:26.082048 | 2011-09-21 22:31:26.082048
> (1 row)
>
That's odd because there is no 'utc -6' timezone. Moreover, 'utc+6' [1] should
be 6 hours ahead 'utc'. I don't read the code to confirm if it is a bug or a
correct behavior (as I don't understand much about the insane timezone rules).

> The rules for interpreting time zone specifications are arcane enough
> to make me suspect that this isn't a bug even though it seems rather
> odd, but in any case it would be useful to know how many hours
> PostgreSQL's timestamp is behind (or ahead of) UTC and similarly for
> the operating system.
>
I think the OP is talking about one of these timezones:

euler=# select * from pg_timezone_names where utc_offset = '-06:00:00';
            name           | abbrev | utc_offset | is_dst
--------------------------+--------+------------+--------
  Mexico/BajaSur           | MDT    | -06:00:00  | t
  Pacific/Galapagos        | GALT   | -06:00:00  | f
  US/Mountain              | MDT    | -06:00:00  | t
  Canada/Mountain          | MDT    | -06:00:00  | t
  Canada/Saskatchewan      | CST    | -06:00:00  | f
  Canada/East-Saskatchewan | CST    | -06:00:00  | f
  America/Swift_Current    | CST    | -06:00:00  | f
  America/Denver           | MDT    | -06:00:00  | t
  America/Chihuahua        | MDT    | -06:00:00  | t
  America/Belize           | CST    | -06:00:00  | f
  America/Costa_Rica       | CST    | -06:00:00  | f
  America/Shiprock         | MDT    | -06:00:00  | t
  America/Managua          | CST    | -06:00:00  | f
  America/Tegucigalpa      | CST    | -06:00:00  | f
  America/Guatemala        | CST    | -06:00:00  | f
  America/Cambridge_Bay    | MDT    | -06:00:00  | t
  America/Regina           | CST    | -06:00:00  | f
  America/Ojinaga          | MDT    | -06:00:00  | t
  America/Yellowknife      | MDT    | -06:00:00  | t
  America/El_Salvador      | CST    | -06:00:00  | f
  America/Edmonton         | MDT    | -06:00:00  | t
  America/Mazatlan         | MDT    | -06:00:00  | t
  America/Boise            | MDT    | -06:00:00  | t
  America/Inuvik           | MDT    | -06:00:00  | t
  MST7MDT                  | MDT    | -06:00:00  | t
  Navajo                   | MDT    | -06:00:00  | t
  Etc/GMT+6                | GMT+6  | -06:00:00  | f
(27 registros)

... and I suspect the is_dst is true.


[1] http://en.wikipedia.org/wiki/UTC%2B6


--
    Euler Taveira de Oliveira - Timbira       http://www.timbira.com.br/
    PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento