2014-02-27 20:10 GMT+01:00 Alvaro Herrera <alvhe...@2ndquadrant.com>:
> 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. > I found a small issue. Routines for parsing time zone expects so time zone starts with '+' or '-'. When this symbol is missing, then it use '-' as default. That is pretty stupid - probably it expects check in preprocessing postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '-1'); make_timestamptz ------------------------ 2014-12-10 12:10:10+01 (1 row) postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '1'); make_timestamptz ------------------------ 2014-12-10 12:10:10+01 (1 row) postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10, '+1'); make_timestamptz ------------------------ 2014-12-10 10:10:10+01 (1 row) When I fix this, then make_timestamptz produce same results as timestamptz input function. CREATE OR REPLACE FUNCTION public.tryt1(integer) RETURNS TABLE (tz int, tm01 timestamptz, tm02 timestamptz, diff interval) LANGUAGE plpgsql AS $function$ declare tz int; begin for tz in - $1 .. $1 loop begin tryt1.tz = tz; tm01 := format('1987-02-14 12:25:00 %s%s', CASE WHEN tz > 0 THEN '+' ELSE '' END, tz)::timestamptz; tm02 := make_timestamptz(1987, 2, 14, 12, 25, 00, CASE WHEN tz > 0 THEN '+' ELSE '' END || tz::text); diff := tm02 - tm01; return next; exception when others then null; raise notice 'error %s: %', SQLERRM, tz; end; end loop; end; $function$; A allowed (or disallowed) numeric zones are little bit strange - but it is different issue not related to this patch. so still I prefer to allow numeric time zones. What I can: a) disallow numeric only timezone without prefix "+" or "-" or b) add "+" prefix to time zone, when number is possitive. I prefer @a. What do you thinking? Regards Pavel > > -- > Álvaro Herrera http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >