Re: Fwd: patch: make_timestamp function

Pavel Stehule escribió:
> Hello
>
> updated patch without timetz support

Great, thanks.

While testing, I noticed something strange regarding numeric timezone
specification. Basically the way any particular value is handled is
underspecified, or maybe just completely wacko. Consider the attached
function, which will try to construct a timestamptz value with all
possible values for timezone in the -1000 to 1000 range, ignoring those
that cause errors for whatever reason, and then subtract the obtained
timestamptz from the base value. The output is also attached.

First of all you can see that there are plenty of values for which the
constructor will simply fail.

Second, the way signs are considered or not seems arbitrary. Note that
if you say either '-2' or '2', you will end up with the same timestamptz
value. But at -16 the value jumps to the opposite sign.

For negative values, this continues up to -99; but at -100, apparently
it stops considering the value a number of hours, and it considers
hours-and-minutes with a missing colon separator. Which works up to
-159; at -160 and up to -167 it uses a different interpretation again
(not sure what). Then values -168 and below are not valid; -200 is
valid again (2 hours) For the rest of the interval,

For positive values, apparently there's no funny interpretation; the
number is taken to be a number of hours up to 167. There's no valid
value above that. However, if you prepend a plus sign, the result is
completely different and there are valid values up to +1559. The funny
behavior in +160 through +167 is there too.

Not sure what to make of this; certainly it's not my interest to fix it.
However I wonder if we should really offer the capability to pass
numeric timezone values. Seems it'd be saner to allow just symbolic
names, either abbreviations or full names.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services