Re: Does PG really lack a time zone for India?

"Ken Winter" <ken(at)sunward(dot)org> writes:
> The documentation
> (http://www.postgresql.org/docs/7.4/static/datetime-keywords.html) =
> doesn't
> have an entry for Indian Standard Time, nor for any other time zone with =
> a
> GMT+5:30 offset.

I don't see any such entry in datetktbl in datetime.c, either. You
could enter it explicitly as +5:30, though, and (as of PG 8.0) there
are settings in the main timezone database for India. (In PG 7.4
it'd depend on what zone names your operating system knows.)

regression=# set timezone = 'GMT';
SET
regression=# select now();
now
-------------------------------
2006-02-15 05:20:33.317049+00
(1 row)

regression=# select '2006-02-15 05:20:33.317049+05:30'::timestamptz;
timestamptz
-------------------------------
2006-02-14 23:50:33.317049+00
(1 row)

regression=# set timezone = 'Asia/Calcutta';
SET
regression=# select now();
now
----------------------------------
2006-02-15 10:51:19.241808+05:30
(1 row)

The existence of duplicate timezone abbreviations is certainly a pain
:-(. The solution I would like to see is to factor all the hardwired
timezone abbreviations in datetktbl out into a configuration file that
could be adjusted for local conditions. However, it's not entirely
clear how to deal with words that could be either a zone name or some
other date keyword, for instance "SAT" is not just a day of the week
but a known zone name in Australia.

Plan B would be to extend the existing "australian_timezones" hack with
some other specialized options, but I think that way madness lies ...

Anyway, what this area needs is for somebody to get annoyed enough
to design and then code a generally acceptable solution.

regards, tom lane