Re: [GENERAL] Does PG really lack a time zone for India?
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. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Does PG really lack a time zone for India?
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 15, 2006 5:39 PM
To: [EMAIL PROTECTED]
Cc: 'PostgreSQL pg-general List'
Subject: Re: [GENERAL] Does PG really lack a time zone for India?
Ken Winter [EMAIL PROTECTED] 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
Re: [GENERAL] Does PG really lack a time zone for India?
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. signature.asc Description: Digital signature
Re: [GENERAL] Does PG really lack a time zone for India?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does PG really lack a time zone for India?
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:[EMAIL PROTECTED] Sent: Wednesday, February 15, 2006 12:34 AM To: [EMAIL PROTECTED] Cc: 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Does PG really lack a time zone for India? Ken Winter [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does PG really lack a time zone for India?
Ken Winter [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does PG really lack a time zone for India?
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 15, 2006 10:25 AM To: [EMAIL PROTECTED] Cc: 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Does PG really lack a time zone for India? Ken Winter [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does PG really lack a time zone for India?
Ken Winter [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Does PG really lack a time zone for India?
The documentation (http://www.postgresql.org/docs/7.4/static/datetime-keywords.html) doesnt 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 PostgreSQLs 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: [GENERAL] Does PG really lack a time zone for India?
Ken Winter [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
