Re: generate_series for timestamptz and time zone problem

Dear colleagues,
Please let me know what is the convention (procedure) of adding new
functions to pg_proc. Specifically how oid is allocated.
This will allow me to continue working on the patch.

I have to extend the timestamptz_pl_interval function, which is in fact
an addition operator. But an additional parameter is needed to specify
the timezone.
Therefore, should I add a second function timestamptz_pl_interval with
three arguments, or should a function with a different name be added so
that it does not get confused with operator functions (which only have
two arguments)?
What is the proposed name for such a function (add(timestamptz,
interval, timezone), date_add(timestamptz, interval, timezone), ...)?

Przemysław Sztoch wrote on 01.06.2022 16:45:
>
>
> Tom Lane wrote on 31.05.2022 22:54:
>> =?UTF-8?Q?Przemys=c5=82aw_Sztoch?=<przemyslaw(at)sztoch(dot)pl> writes:
>>> |generate_series| ( /|start|/ |timestamp with time zone|, /|stop|/
>>> |timestamp with time zone|, /|step|/ |interval| )
>>> produces results depending on the timezone value set:
>> That's intentional. If you don't want it, maybe you should be using
>> generate_series on timestamp without time zone?
>>
>> regards, tom lane
> 1. Of course it is intentional.  And usually everything works as it
> should.
>
> But with multi-zone applications, using timestamptz generates a lot of
> trouble.
> It would be appropriate to supplement a few functions with the
> possibility of specifying a zone (of course, for timestamptz variants):
> - generate_series
> - date_bin (additionally  with support for months and years)
> - timestamptz_plus_interval (the key issue is adding months and years,
> "+" operator only does this in the local zone)
>
> Not everything can be solved by converting the time between
> timestamptz and timestamp (e.g. using the timezone function).
> Daylight saving time reveals additional problems that are not visible
> at first glance.
>
> Just if DST did not exist, a simple conversion (AT TIME ZONE '...')
> would have been enough.
> Unfortunately, DST is popular and, additionally, countries modify
> their time zones from time to time.
>
> 2. Because I lack the necessary experience, I want to introduce
> changes in parts.
> There is patch for first function timestamptz_plus_interval.
>
> I don't know how to properly correct pg_proc.dat and add a variant of
> this function with 3 arguments now.
>
> Please comment on the patch and provide tips for pg_proc.
> If it works for me, I will improve generate_series.
>
> --
> Przemysław Sztoch | Mobile +48 509 99 00 66

--
Przemysław Sztoch | Mobile +48 509 99 00 66