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

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, February 15, 2006 10:25 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:
> > 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.
>
> I think you're thinking about this in entirely the wrong fashion.
>
> What you are really saying is that you want to deal with absolute time:
> the payment deadline is a fixed time instant and you don't want the
> observer's timezone to affect the decision about whether the deadline
> has passed or not.  The way to do that in Postgres is to store all
> timestamps as TIMESTAMP WITH TIME ZONE and not do any explicit timezone
> translations.  When you enter a timestamp value, either write the
> correct GMT offset in it, eg '2006-02-15 10:22:46-05', or leave it out
> and the database will assume that it's expressed in the current TimeZone
> zone.  Either way, it'll get converted to UTC internally and all
> subsequent comparisons are absolute.
>
>             regards, tom lane

Yes, that's what I'm trying to do.  My problem has been: how to enter the
equivalent of '2006-02-15 10:22:46-05' when the time I want to enter and
convert to EST is a variable value or now().

I've finally figured out the answer - do data entry through:
    <timestamptz variable value> AT TIME ZONE 'GMT' + '-5:00'
and
    now() AT TIME ZONE 'GMT' + '-5:00'
You can substitute a variable of type "interval" for the '-5:00' constant.

These could of course also be accomplished by:
    <timestamptz variable value> AT TIME ZONE 'EST'
and
    now() AT TIME ZONE 'EST'
But the first solution bypasses PostgreSQL's incomplete list of time zone
codes.  So it can be used to handle the missing time codes for (for example)
India (GMT+5:30) and Nepal (GMT+5:45), which is what I need.

~ Thanks to all for the help!
~ Ken