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
diff --git a/src/backend/utils/adt/timestamp.c
b/src/backend/utils/adt/timestamp.c
index f70f829d83..8ad4b3380f 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3003,83 +3003,124 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
{
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
Interval *span = PG_GETARG_INTERVAL_P(1);
- TimestampTz result;
+ pg_tz *attimezone = NULL;
int tz;
if (TIMESTAMP_NOT_FINITE(timestamp))
- result = timestamp;
- else
- {
- if (span->month != 0)
- {
- struct pg_tm tt,
- *tm = &tt;
- fsec_t fsec;
-
- if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL)
!= 0)
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
+ PG_RETURN_TIMESTAMP(timestamp);
- tm->tm_mon += span->month;
- if (tm->tm_mon > MONTHS_PER_YEAR)
- {
- tm->tm_year += (tm->tm_mon - 1) /
MONTHS_PER_YEAR;
- tm->tm_mon = ((tm->tm_mon - 1) %
MONTHS_PER_YEAR) + 1;
- }
- else if (tm->tm_mon < 1)
- {
- tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
- tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR +
MONTHS_PER_YEAR;
- }
+ if (PG_NARGS() > 2)
+ {
+ text *zone = PG_GETARG_TEXT_PP(2);
+ char tzname[TZ_STRLEN_MAX + 1];
+ char *lowzone;
+ int type,
+ val;
+ pg_tz *tzp;
+ /*
+ * Look up the requested timezone (see notes in
timestamptz_zone()).
+ */
+ text_to_cstring_buffer(zone, tzname, sizeof(tzname));
- /* adjust for end of month boundary problems... */
- if (tm->tm_mday >
day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
- tm->tm_mday =
(day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
+ /* DecodeTimezoneAbbrev requires lowercase input */
+ lowzone = downcase_truncate_identifier(tzname,
+
strlen(tzname),
+
false);
- tz = DetermineTimeZoneOffset(tm, session_timezone);
+ type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
- if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
+ if (type == TZ || type == DTZ)
+ {
+ /* fixed-offset abbreviation, get a pg_tz descriptor
for that */
+ tzp = pg_tzset_offset(-val);
}
-
- if (span->day != 0)
+ else if (type == DYNTZ)
{
- struct pg_tm tt,
- *tm = &tt;
- fsec_t fsec;
- int julian;
-
- if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL)
!= 0)
+ /* dynamic-offset abbreviation, use its referenced
timezone */
+ }
+ else
+ {
+ /* try it as a full zone name */
+ tzp = pg_tzset(tzname);
+ if (!tzp)
ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("time zone \"%s\" not
recognized", tzname)));
+ }
+ attimezone = tzp;
+ }
- /* Add days by converting to and from Julian */
- julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
span->day;
- j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ /* Use session timezone if caller asks for default */
+ if (attimezone == NULL)
+ attimezone = session_timezone;
+
+ if (span->month != 0)
+ {
+ struct pg_tm tt,
+ *tm = &tt;
+ fsec_t fsec;
- tz = DetermineTimeZoneOffset(tm, session_timezone);
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
- if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
+ tm->tm_mon += span->month;
+ if (tm->tm_mon > MONTHS_PER_YEAR)
+ {
+ tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
+ tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
+ }
+ else if (tm->tm_mon < 1)
+ {
+ tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
+ tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR +
MONTHS_PER_YEAR;
}
- timestamp += span->time;
+ /* adjust for end of month boundary problems... */
+ if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
+ tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon
- 1]);
- if (!IS_VALID_TIMESTAMP(timestamp))
+ tz = DetermineTimeZoneOffset(tm, attimezone);
+
+ if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
+ }
- result = timestamp;
+ if (span->day != 0)
+ {
+ struct pg_tm tt,
+ *tm = &tt;
+ fsec_t fsec;
+ int julian;
+
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ /* Add days by converting to and from Julian */
+ julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
span->day;
+ j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+
+ tz = DetermineTimeZoneOffset(tm, attimezone);
+
+ if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
}
- PG_RETURN_TIMESTAMP(result);
+ timestamp += span->time;
+
+ if (!IS_VALID_TIMESTAMP(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ PG_RETURN_TIMESTAMP(timestamp);
}
Datum
