Re: Fwd: patch: make_timestamp function

2014-02-27 20:10 GMT+01:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)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
>