Re: Fwd: patch: make_timestamp function - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: Fwd: patch: make_timestamp function |
| Date | |
| Msg-id | CAFj8pRCWR3b5oHvRKBr=6khTaFLq31u_2p57zxCxcQyiikBskg@mail.gmail.com Whole thread Raw |
| In response to | Re: Fwd: patch: make_timestamp function (Alvaro Herrera <alvherre@2ndquadrant.com>) |
| Responses |
Re: Fwd: patch: make_timestamp function
(Alvaro Herrera <alvherre@2ndquadrant.com>)
|
| List | pgsql-hackers |
2014-02-27 20:10 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
Pavel Stehule escribió:> HelloGreat, thanks.
>
> updated patch without timetz support
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)
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$;
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
Pavel
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
pgsql-hackers by date: