Re: API bug in DetermineTimeZoneOffset()

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, przemek(at)hadapt(dot)com
Subject: Re: API bug in DetermineTimeZoneOffset()
Date: 2013-10-31 18:54:57
Message-ID: 5556.1383245697@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wrote:
> Actually, it strikes me there might be another way to do this, which is to
> get rid of HasCTZSet/CTimeZone entirely in favor of consing up some pseudo
> pg_tz structure that represents the desired semantics when we want a
> "brute force" setting.

After some study of the pgtz code, it turns out this is absolutely trivial
to do by using the POSIX syntax for time zone names. You can do something
like

set timezone to '<GMT+01:30>-01:30';

where the stuff between angle brackets is pretty much arbitrary and is
used as the zone abbreviation for printout purposes. So for example,
after the above I get

regression=# select now(), timeofday();
now | timeofday
----------------------------------+-------------------------------------------
2013-10-31 20:02:54.130828+01:30 | Thu Oct 31 20:02:54.130988 2013 GMT+01:30
(1 row)

(It's worth noting that timeofday() is another operation that doesn't
currently give sane results when HasCTZSet is true --- it prints a time
that matches the brute force zone, with a zone abbreviation that doesn't.)

So what I'm thinking we should do is internally translate SET TIMEZONE
with an interval value into a POSIX-style zone name in the above format,
and then just flush HasCTZSet/CTimeZone and all the special case logic
around them.

This would create a couple of user-visible incompatibilities:

1. The zone abbreviation printed by timeofday(), to_char(), etc would now
become "GMT+-hh[:mm[:ss]]" (or whatever we choose to stick in the angle
brackets, but that's what I'm thinking). However, since those
abbreviation printouts were just completely wrong before, this doesn't
seem like something people could complain about.

2. The value printed by SHOW TIMEZONE would change format. Now you
get

regression=# set time zone '-1.5';
SET
regression=# show time zone;
TimeZone
-----------
-01:30:00
(1 row)

and what I'm proposing is to let it print the POSIX zone name, which
in this case would be <GMT-01:30>+01:30 (note the sign incompatibility
between POSIX and ISO ...). If anybody is sufficiently bothered by this
then we could add a kludge to show_timezone to replicate the old
printout, but I doubt it's a big deal. Again, we know that very darn
few people are using the brute-force zone feature at all (else we'd have
heard complaints sooner), so how many apps are likely to care about the
exact format of SHOW TIME ZONE output for this case?

An intermediate position would be to include the printout kludge in
the back-branch patches and then take it out in HEAD, so that the
change in printout behavior only appears as of 9.4.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Browne 2013-10-31 19:08:49 Re: How can I build OSSP UUID support on Windows to avoid duplicate UUIDs?
Previous Message Dann Corbit 2013-10-31 18:53:24 postgresql-9.3.1-1-windows-x64.exe does not install correctly for me