Re: Does PG really lack a time zone for India? - Mailing list pgsql-general

Tom ~

Thanks for yet another prompt and helpful response.  May I submit a
follow-up question?

Briefly, what I'm trying to do is build a (web-front-ended) system that
translates a "now()" entry into a timestamptz column into "now" at a
specified time zone (which is looked up from the database).

Why?  Say there's a payment deadline recorded in a database column, and that
deadline is supposed to be as of the local time of the office that accepted
the order.  The database has tables and columns that record the time zone of
each office and associate each order with the office that accepted it.  Say
there's an actual payment column that records the actual time when a payment
is received, and that column is compared to the deadline column to determine
whether to charge a late fee.  Say I place an order with the London office
(GMT), but I am in California (GMT+8) (and the PostgreSQL server is there,
too).  If I pay one hour before the deadline, CA time, I should be charged a
late fee, because the deadline actually occurred 7 hours ago in London.  To
have that come out right, the system needs to translate "now()" on input to
"now() AT TIME ZONE 'GMT'", so that the timestamp that gets stored in the
payment column is 7 hours after the deadline.  As far as I can tell, that
works fine - that is, I submit such a query to PostgreSQL and it returns the
correct time in that zone.  The problem comes when PostgreSQL has no time
zone code that gives me the GMT offset that I need - most painfully, that is
the case with India (GMT+5:30).

So, I'm wondering if the "AT TIME ZONE" construct can accept the offset in
any syntax that isn't dependent on the time zone code.  I tried entries such
as "now() AT TIME ZONE 'GMT+5:30'" and "now() AT TIME ZONE '+5:30'", but
they didn't work.  Any suggestions?

~ Thanks again
~ Ken

PS: If it would be of any help, I would be happy to share the "country uses
timezone" table that I cobbled together yesterday, mapping the existing PG
7.4 time zone codes (sometimes renamed) to a country list derived from lists
of countries that have postal codes and/or international dialing prefixes.
It's yet another hack, but hey it's free for the asking.  (Given the lack of
a worldwide standard for time zone names and abbreviations, any such effort
is going to be something of a hack; I think the best we can hope for is a
list of names and codes that most people can recognize, but a fully correct
list of the GMT offsets.)

PPS:  I'm glad that PG 8.x has discovered India.  Alas, my web host informs
me that I'm stuck with 7.4 until a production version of the psycopg2
connector comes out.


> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, February 15, 2006 12:34 AM
> To: ken@sunward.org
> Cc: 'PostgreSQL pg-general List'
> Subject: Re: [GENERAL] Does PG really lack a time zone for India?
>
> "Ken Winter" <ken@sunward.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