Re: Timezone issues with Postrres

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pratikchirania <pratik(dot)chirania(at)hp(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org, Magnus Hagander <magnus(at)hagander(dot)net>
Subject: Re: Timezone issues with Postrres
Date: 2011-09-22 05:42:54
Message-ID: 10216.1316670174@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

pratikchirania <pratik(dot)chirania(at)hp(dot)com> writes:
> 1. I am in Costa Rica, using Windows Server 2008 R2/Windows 2003 with
> PostgreSQL 8.3/9.0
> 2. System Date/Time Settings shows "CST/Central America" with UTC-6 as extra
> display
> 4. There is NO DST for CST (Central America) a.k.a America/Costa_Rica to the
> PostgreSQL database through the pg_timezone_names view

Well, if you want no-DST behavior, this is wrong:

> show timezone
> "CST6CDT"

That timezone specifies daylight savings behavior (CDT). As it happens,
it's going to follow the USA rules for when to switch, but any switch
would be wrong for Costa Rica. You need the America/Costa_Rica setting.

Now having said that, it appears that the reason you got "CST6CDT" by
default is that we map the Windows "Central America Standard Time" and
"Central America Daylight Time" registry strings to that. This seems
clearly wrong. A look at the tzdata "northamerica" file shows that
noplace in Central America other than Mexico has observed DST with any
regularity, and certainly none of them could be said to follow USA DST
rules.

Mexico is a separate case, because there are separate "Central Standard
Time (Mexico)" and "Central Daylight Time (Mexico)" entries, which we
map to "America/Mexico_City", which seems proper.

I think we ought to map "Central America Standard Time" to plain CST6.
(Or we could map to one of America/Costa_Rica, America/Guatemala,
America/El_Salvador, etc, but that seems more likely to offend people in
the other countries than provide any additional precision.) I am not
sure what we ought to do with "Central America Daylight Time", but on
the evidence here I wonder whether that setting exists in the wild at
all.

Magnus, AFAICT from the commit logs, that lookup table was your work to
begin with --- do you remember anything about the reasoning for the
Central America entries?

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2011-09-22 11:30:29 Re: [v9.2] make_greater_string() does not return a string in some cases
Previous Message pratikchirania 2011-09-22 04:47:35 Re: Timezone issues with Postrres