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

Does PG really lack a time zone for India?

From
"Ken Winter"
Date:

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. 

 

Is this just an omission from the documentation?  If so, what are the name and codes of the GMT+5:30 time zone? 

 

Or does PostgreSQL really have no time zone that it can read for this rather important place?  If so, can this problem be fixed? 

 

(I believe the standard code for Indian Standard Time is IST, which alas conflicts with PostgreSQL’s code for Israel Standard Time, so I guess some other code needs to be used for India.)

 

The documentation and/or PostgreSQL itself are also missing some other GMT offsets:

 

  • GMT+5:45 (Nepal)
  • GMT+11 (various mid-Pacific islands) – There actually is one zone with this offset, but it is an Australian daylight saving time zone (AESST), so using it for standard time is a bit untidy.
  • GMT+11:30 (Norfolk Island)
  • GMT+13 and GMT+14 (Kribali, Tonga) – Yes indeed, these Pacific islands are actually more than 12 hours ahead of GMT!

 

These are less important than India because (except for Nepal) they only apply to a few Pacific islands, but if someone were to go in there and fix the India problem, it might be a good time to tidy these up as well.

 

~ TIA

~ Ken

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

From
Tom Lane
Date:
"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

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

From
Martijn van Oosterhout
Date:
On Wed, Feb 15, 2006 at 12:33:30AM -0500, Tom Lane wrote:
> 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.

I really wish we could clear up this stuff with the australian
timezones. I'd love a poll as to how often they're used because I don't
think most people want them. We run a business in Australia with plenty
of timezone related stuff yet that hack remains firmly off. For
example, SAT being South Australian Time is something I never heard of.
It's the same timezone as in Northern Territory. Maybe it's an old
term, since NT was part of SA before 95 years ago.

Australian timezones are East, Central and West plus daylight savings
for some states. If you search google for "sat south australia
timezone" most of the matches you get are for the postgresql
documentation. Most of the others either refer to ACST/CST or have SAT
as -9:00 [1] which is not a standard timezone anywhere in australia.

Wikipedia doesn't mention it [2]. This one reference [3] lists it as
alternate. But whacked out timezones like ACSST/AESST barely exist
outside of the postgres documentation. Try googling for something like
"aesst timezone -postgresql -postgres -pgsql" and you get a very small
set, much of which is postgresql related anyway (people which copied
our list).

The solution is to allow the timezone portion to be a string like
"Australia/Adelaide" and to leave these three letter timezones behind.
I made it work for my own timestamp type, so it can't be that hard.

Have a nice day,

[1] http://www.mhonarc.org/MHonArc/doc/resources/timezones.html
[2] http://en.wikipedia.org/wiki/UTC9:30
[3] http://www.astrodatabank.com/DCH/50alternatetimezonenames.htm
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment

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

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> I really wish we could clear up this stuff with the australian
> timezones. I'd love a poll as to how often they're used because I don't
> think most people want them.

I think defining the problem as "let's get rid of australian_timezones"
would be a serious mistake.  The basic problem here is that we can't
have a one-size-fits-all list of timezone abbreviations.  We've
certainly heard plenty of complaints about IST, and I seem to recall
some from Brazil, and there are other conflicts noted in the comments
in the existing list.  So even if there is no one who cares anymore
about australian_timezones (which I doubt, 'cause that code isn't all
that old), we still have a problem.

I want to fix it so users can make up their own minds and stop pestering
us ;-).  (Or more accurately, I want someone else to fix it ... it's not
high enough on my own want-list that I'd do it myself soon.)  That would
cause the australian_timezones parameter in its current form to go away,
but it wouldn't simply be a feature-ectomy.

> The solution is to allow the timezone portion to be a string like
> "Australia/Adelaide" and to leave these three letter timezones behind.

While I'd certainly like to see us allow the long forms of timezone
names within data input, you're living in a dream world if you think
that people will be willing to type, eg, "Americas/New_York" every time
where they had been used to entering "EST".  We need to support the
abbreviations too.

            regards, tom lane

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

From
"Ken Winter"
Date:
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


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

From
Tom Lane
Date:
"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

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

From
Martijn van Oosterhout
Date:
Comments inline.

On Wed, Feb 15, 2006 at 09:49:57AM -0500, Tom Lane wrote:
> I think defining the problem as "let's get rid of australian_timezones"
> would be a serious mistake.  The basic problem here is that we can't
> have a one-size-fits-all list of timezone abbreviations.  We've
> certainly heard plenty of complaints about IST, and I seem to recall
> some from Brazil, and there are other conflicts noted in the comments
> in the existing list.  So even if there is no one who cares anymore
> about australian_timezones (which I doubt, 'cause that code isn't all
> that old), we still have a problem.

Hmm? The original USE_AUSTRALIAN_RULES timezones were added June
1997[1] for 6.1 and the #define was changed to a GUC in June 2001 [2]
in time for 7.2. The code has been there for ages.

It's funny how it was added though. Someone mentioned the issue in 1997
and said it would be nice to handle, even if it was just via a #define
[3]. Two days later without further discussion the hack was added.

I'm more suggesting some of the totally unused ones (AESST/ACSST) being
removed. I can't find the history of those. They were in the very first
patch to the date/time code from Postgres95 (rev 1.3 of dt.c) but given
they're not known by anyone else I wonder where the list came from.

> > The solution is to allow the timezone portion to be a string like
> > "Australia/Adelaide" and to leave these three letter timezones behind.
>
> While I'd certainly like to see us allow the long forms of timezone
> names within data input, you're living in a dream world if you think
> that people will be willing to type, eg, "Americas/New_York" every time
> where they had been used to entering "EST".  We need to support the
> abbreviations too.

Ah, but the zic library defines EST just fine, so nothing would change
there. You just picked two that are equivalent. Thing is, ACST and
Australia/Adelaide are not equivalent due to daylight savings.

In the timestamp type I created, I simply have a table with all the
timezones in it. If a user wants IST to be something else, they simply
change the table. It's somewhat unusual compared to the way we do most
types, but is there anything inheritly wrong with that approach?

[1] http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/Attic/dt.c.diff?r1=1.25;r2=1.26;f=h
[2]
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/misc/postgresql.conf.sample.diff?r1=1.11;r2=1.12;f=h
[3] http://archives.postgresql.org/pgsql-hackers/1997-06/msg00400.php

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment

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

From
"Ken Winter"
Date:
> -----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


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

From
Tom Lane
Date:
"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

Pg_hba.conf issues

From
Colin Shreffler
Date:
I am not able to successfully create an entry in the pg_hba.conf file to
enable remote access to my postgres database.

The postmaster process HAS been initialized to accept tcp/ip connections.

I have tried entering the following record in the file, but it does not
grant me access.

host      all      all      192.168.0.0      255.255.255.0     trust

NOTE: I'm trying to access the server using pgAdmin 3 on my dev machine.
Dev/Client machine is OS X Tiger and server is OS X Tiger Server.

I would think that this would be the least restrictive access for our local
area network.  I'm trying to get it to work in the least secure mode first
and then tighten down the security after I know its working.

Does anyone have any ideas?

Thanks,
Colin Shreffler



Re: Pg_hba.conf issues

From
Tom Lane
Date:
Colin Shreffler <colin.shreffler@warp9software.com> writes:
> I have tried entering the following record in the file, but it does not
> grant me access.

(1) did you remember to sighup the postmaster after changing the file?
(2) what error message do you get *exactly*?  What shows up in the
postmaster log file?

            regards, tom lane

Re: Pg_hba.conf issues

From
"Raymond O'Donnell"
Date:
On 15 Feb 2006 at 15:44, Colin Shreffler wrote:

> host      all      all      192.168.0.0      255.255.255.0     trust

Is there any other, more restrictive, line *above* this one in the
file? The order of entries does seem to make a difference.

--Ray.

-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@iol.ie                          Galway Cathedral Recitals
-------------------------------------------------------------


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

From
"Ken Winter"
Date:
> -----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

Attachment

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

From
Bruce Momjian
Date:
Martijn van Oosterhout wrote:
> Comments inline.
>
> On Wed, Feb 15, 2006 at 09:49:57AM -0500, Tom Lane wrote:
> > I think defining the problem as "let's get rid of australian_timezones"
> > would be a serious mistake.  The basic problem here is that we can't
> > have a one-size-fits-all list of timezone abbreviations.  We've
> > certainly heard plenty of complaints about IST, and I seem to recall
> > some from Brazil, and there are other conflicts noted in the comments
> > in the existing list.  So even if there is no one who cares anymore
> > about australian_timezones (which I doubt, 'cause that code isn't all
> > that old), we still have a problem.
>
> Hmm? The original USE_AUSTRALIAN_RULES timezones were added June
> 1997[1] for 6.1 and the #define was changed to a GUC in June 2001 [2]
> in time for 7.2. The code has been there for ages.
>
> It's funny how it was added though. Someone mentioned the issue in 1997
> and said it would be nice to handle, even if it was just via a #define
> [3]. Two days later without further discussion the hack was added.

As I remember, the problem was that AST was used both for Atlantic
Standard Time (think eastern Canada) and Australia, and we had users in
both time zones.

Fortunately that was the only overlap we commonly saw for years.  Only
recently have we hit more, specifically IST for Israel and India, I
think.  Anyway, now that we have the tz database in PostgreSQL, we can
use the long names, so the abbreviations are only for convenience.  We do
have a TODO item on this:

        o Allow customization of the known set of TZ names (generalize the
          present australian_timezones hack)

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +