"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
