Re: Timezone issues with Postrres

On 21-09-2011 13:38, Robert Haas wrote:
> On Wed, Sep 21, 2011 at 3:16 AM, pratikchirania<pratik(dot)chirania(at)hp(dot)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