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?=<przemys...@sztoch.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
