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 5:39 PM
> 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:
> > 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().
>
> This still shows a problem in your grasp of what's going on.  now() is
> absolute and should never need to be converted ...
>
>             regards, tom lane

Tom ~

OK, let me show a little example, in hopes that you can show where I'm
making things needlessly complicated:

/* The table payment records when particular payments were received and
when they were due.  The requirement is that the due dates are in the local
time of the office receiving the payment, and late fees must be calculated
based on that time.  (In the real world, this very denormalized table would
actually be the result of a query joining many tables, but I'm trying to
distill the essence here.)  */

CREATE TABLE payment (
    office_location character varying NOT NULL,
    -- City of the office to which the payment is due
    office_time_zone character varying(10),
    -- PostgreSQL time zone code of the office where the payment is due.

    -- I had to lie about Bangalore, because PostgreSQL has no code
    -- for Indian Time (GMT+5:30) and the payment_calcs view evokes
    -- an error if not given a valid tz code.
    office_gmt_offset interval,
    -- Offset of office time zone from GMT
    due_date_in_office_time_zone timestamp with time zone,
    -- Timestamp for when the pmt is due.
    -- IMPORTANT: This is interpreted as a time in the office's time
zone.
    -- Note that they due dates all the same clock-time, namely 5 pm.
    paid_date_in_server_time_zone timestamp with time zone
    -- The time when the payment was received.  As these payments were
    -- all entered with no tz modifier, the time entered was interpreted
    -- as the server's time zone, which happens to be CST (GMT-06).
    -- IMPORTANT: The payment date was entered as a literal, not as
    -- now(), but that makes no difference; once the timestamp is
    -- stored it is impossible to tell which way it was entered.
);

/* This view makes several calculations from the payment table that may help
understand what is going on.  The most important is
paid_date_in_office_time, because that is the one that I believe has to be
used in the real system, either when the payment time is entered or when
the late calcs are being made, in order to get the right results in terms
of assessing late fees. */

CREATE VIEW payment_calcs AS
    SELECT
    payment.office_location,
    payment.office_time_zone,
    payment.office_gmt_offset,
    payment.due_date_in_office_time_zone,
    payment.paid_date_in_server_time_zone,
    (payment.due_date_in_office_time_zone - payment.office_gmt_offset)
        AS due_date_in_gmt,
        -- The GMT when the payment is due.
    timezone('GMT'::text, payment.paid_date_in_server_time_zone)
        AS paid_date_in_gmt,
        -- The GMT when the payment was received.
    (timezone('GMT'::text,
        payment.paid_date_in_server_time_zone) +
        payment.office_gmt_offset) AS paid_date_in_office_time,
        -- The time when the payment was received, converted into
        -- the time zone of the receiving office.
    ((timezone((payment.office_time_zone)::text,
        payment.paid_date_in_server_time_zone))::timestamp
        with time zone - payment.due_date_in_office_time_zone)
        AS late_by_time_zone_code,
        -- The time interval between the due date and the payment,
        -- calculated using the PostgreSQL time zone code.
        -- Note that the Bangalore result is wrong by 1/2 hour
        -- because the nearest PG code is wrong by 1/2 hour.
    (((timezone('GMT'::text, payment.paid_date_in_server_time_zone) +
        payment.office_gmt_offset))::timestamp with time zone
        - payment.due_date_in_office_time_zone) AS late_by_offset
        -- The time interval between the due date and the payment,
        -- calculated using the office_gmt_offset.
        -- Note that the Bangalore result is right
        -- because the offset is right.
FROM payment
ORDER BY (payment.office_gmt_offset)::time without time zone;


INSERT INTO payment VALUES ('Paris', 'CET', '01:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Baghdad', 'BT', '03:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Abu Dhabi', 'RET', '04:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Islamabad', 'MVT', '05:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Athens', 'EET', '02:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Omsk', 'ALMT', '06:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Jakarta', 'CXT', '07:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Bangalore', 'IOT', '05:30:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');


ALTER TABLE ONLY payment
    ADD CONSTRAINT payment_pk PRIMARY KEY (office_location);

In case you don't want to actually set up these objects and try them out, I
attach an HTML page of the results.

So my question is:  Does the notion of "now() is absolute" offer a simpler
way than this (converting the time using a construct like "<payment
timestamp> AT TIME ZONE ('GMT') + <office time zone offset>") to meet the
requirement?

~ Thanks
~ Ken