Re: Timezone issues with Postrres

On Thu, Sep 22, 2011 at 07:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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?

Hmm. not entirely. I know the initial round was basically all just
guesses. Then at some point we added tools/win32tzlist.pl. But IIRC
the actual timezones picked were more or less still guestimates. So I
think it's just a mistake in that, and should be changed.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/