Fwd: patch: make_timestamp function

Hello

2014/1/11 Tomas Vondra <tv(at)fuzzy(dot)cz>

> Hi,
>
> I've done a quick review of this patch:
>
> 1) patch applies fine to the current HEAD, with a few hunks offset
> by a few lines
>
> 2) the compilation fails because of duplicate OIDs in pg_proc, so
> I had to change 3969-3975 to 4033-4039, then it compiles fine
>

fixed

>
> 3) make installcheck works fine
>
> 4) No regression tests for make_time / make_date.
>
> 5) The documentation is incomplete - make_date / make_time are missing.
>

two previous points are done by
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f901bb50e33ad95593bb68f7b3b55eb2e47607dccommits.
This patch try to complete a ToDo entry.

>
> 6) The documentation should mention that when the 'timezone' parameter
> is not set explicitly, the current timezone is used.
>

fixed

>
> 7) Why do the functions accept only the timezone abbreviation, not the
> full name? I find it rather confusing, because the 'timezone' option
> uses the full name, and we're using this as the default. But doing
> 'show timestamp' and using the returned value fails. Is it possible
> to fix this somehow?
>

A only abbreviation is allowed for timetz type. Timestamp can work with
full time zone names. A rules (behave) should be same as input functions
for types: timestamptz and timetz.

postgres=# select '10:10:10 CET'::timetz;
timetz
─────────────
10:10:10+01
(1 row)

postgres=# select '10:10:10 Europe/Prague'::timetz;
ERROR: invalid input syntax for type time with time zone: "10:10:10
Europe/Prague"
LINE 1: select '10:10:10 Europe/Prague'::timetz;
^

This limit is due used routines limits.

postgres=# select make_timestamptz(2014, 12, 10, 10, 10, 10,
'America/Vancouver');
make_timestamptz
────────────────────────
2014-12-10 19:10:10+01
(1 row)

Time: 0.829 ms
postgres=# select '2014-12-10 10:10:10
America/Vancouver'::timestamptz; timestamptz
────────────────────────
2014-12-10 19:10:10+01
(1 row)

Time: 0.753 ms

I enhanced a regress tests. I found so work with time zones is not strongly
consistent in different use cases. Operator AT TIME ZONE is more tolerant,
but I use a routines used in input functions and my target was consistent
behave (and results) with input functions.

Regards

Pavel

>
>
>
> regards
> Tomas
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>