Re: API bug in DetermineTimeZoneOffset()

On Thu, Oct 31, 2013 at 2:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

I think it's pretty important to avoid user-visible changes in the
back-branches, except to the minimum extent necessary to fix overtly
wrong behavior.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company