Add functions to do timestamptz arithmetic in a non-default timezone.

Add versions of timestamptz + interval, timestamptz - interval, and
generate_series(timestamptz, ...) in which a timezone can be specified
explicitly instead of defaulting to the TimeZone GUC setting.

The new functions for the first two are named date_add and
date_subtract.  This might seem too generic, but we could use
overloading to add additional variants if that seems useful.

Along the way, improve the docs' pretty inadequate explanation
of how timestamptz +- interval works.

Przemysław Sztoch and Gurjeet Singh; cosmetic changes and most of
the docs work by me

Discussion: https://postgr.es/m/01a84551-48dd-1359-bf7e-f6b0203a6bd0@sztoch.pl

6 files changed, 291 insertions, 44 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 15314aa3ee5..a3a13b895f2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9268,6 +9268,28 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
 
        <row>
         <entry role="func_table_entry"><para role="func_signature">
+         <indexterm>
+          <primary>date_add</primary>
+         </indexterm>
+         <function>date_add</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
+         <returnvalue>timestamp with time zone</returnvalue>
+        </para>
+        <para>
+         Add an <type>interval</type> to a <type>timestamp with time
+         zone</type>, computing times of day and daylight-savings adjustments
+         according to the time zone named by the third argument, or the
+         current <xref linkend="guc-timezone"/> setting if that is omitted.
+         The form with two arguments is equivalent to the <type>timestamp with
+         time zone</type> <literal>+</literal> <type>interval</type> operator.
+        </para>
+        <para>
+         <literal>date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
+         <returnvalue>2021-10-31 23:00:00+00</returnvalue>
+        </para></entry>
+       </row>
+
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
          <function>date_bin</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> )
          <returnvalue>timestamp</returnvalue>
         </para>
@@ -9316,6 +9338,28 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          <indexterm>
+          <primary>date_subtract</primary>
+         </indexterm>
+         <function>date_subtract</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
+         <returnvalue>timestamp with time zone</returnvalue>
+        </para>
+        <para>
+         Subtract an <type>interval</type> from a <type>timestamp with time
+         zone</type>, computing times of day and daylight-savings adjustments
+         according to the time zone named by the third argument, or the
+         current <xref linkend="guc-timezone"/> setting if that is omitted.
+         The form with two arguments is equivalent to the <type>timestamp with
+         time zone</type> <literal>-</literal> <type>interval</type> operator.
+        </para>
+        <para>
+         <literal>date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
+         <returnvalue>2021-10-30 22:00:00+00</returnvalue>
+        </para></entry>
+       </row>
+
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <indexterm>
           <primary>date_trunc</primary>
          </indexterm>
          <function>date_trunc</function> ( <type>text</type>, <type>timestamp</type> )
@@ -9808,13 +9852,23 @@ SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
 
   <para>
    When adding an <type>interval</type> value to (or subtracting an
-   <type>interval</type> value from) a <type>timestamp with time zone</type>
-   value, the days component advances or decrements the date of the
-   <type>timestamp with time zone</type> by the indicated number of days,
-   keeping the time of day the same.
-   Across daylight saving time changes (when the session time zone is set to a
-   time zone that recognizes DST), this means <literal>interval '1 day'</literal>
-   does not necessarily equal <literal>interval '24 hours'</literal>.
+   <type>interval</type> value from) a <type>timestamp</type>
+   or <type>timestamp with time zone</type> value, the months, days, and
+   microseconds fields of the <type>interval</type> value are handled in turn.
+   First, a nonzero months field advances or decrements the date of the
+   timestamp by the indicated number of months, keeping the day of month the
+   same unless it would be past the end of the new month, in which case the
+   last day of that month is used.  (For example, March 31 plus 1 month
+   becomes April 30, but March 31 plus 2 months becomes May 31.)
+   Then the days field advances or decrements the date of the timestamp by
+   the indicated number of days.  In both these steps the local time of day
+   is kept the same.  Finally, if there is a nonzero microseconds field, it
+   is added or subtracted literally.
+   When doing arithmetic on a <type>timestamp with time zone</type> value in
+   a time zone that recognizes DST, this means that adding or subtracting
+   (say) <literal>interval '1 day'</literal> does not necessarily have the
+   same result as adding or subtracting <literal>interval '24
+   hours'</literal>.
    For example, with the session time zone set
    to <literal>America/Denver</literal>:
 <screen>
@@ -22017,13 +22071,17 @@ AND
         <returnvalue>setof timestamp</returnvalue>
        </para>
        <para role="func_signature">
-        <function>generate_series</function> ( <parameter>start</parameter> <type>timestamp with time zone</type>, <parameter>stop</parameter> <type>timestamp with time zone</type>, <parameter>step</parameter> <type>interval</type> )
+        <function>generate_series</function> ( <parameter>start</parameter> <type>timestamp with time zone</type>, <parameter>stop</parameter> <type>timestamp with time zone</type>, <parameter>step</parameter> <type>interval</type> <optional>, <parameter>timezone</parameter> <type>text</type> </optional> )
         <returnvalue>setof timestamp with time zone</returnvalue>
        </para>
        <para>
         Generates a series of values from <parameter>start</parameter>
         to <parameter>stop</parameter>, with a step size
         of <parameter>step</parameter>.
+        In the timezone-aware form, times of day and daylight-savings
+        adjustments are computed according to the time zone named by
+        the <parameter>timezone</parameter> argument, or the current
+        <xref linkend="guc-timezone"/> setting if that is omitted.
        </para></entry>
       </row>
      </tbody>
@@ -22091,6 +22149,25 @@ SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
  2008-03-03 22:00:00
  2008-03-04 08:00:00
 (9 rows)
+
+-- this example assumes that TimeZone is set to UTC; note the DST transition:
+SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
+                              '2001-11-01 00:00 -05:00'::timestamptz,
+                              '1 day'::interval, 'America/New_York');
+    generate_series
+------------------------
+ 2001-10-22 04:00:00+00
+ 2001-10-23 04:00:00+00
+ 2001-10-24 04:00:00+00
+ 2001-10-25 04:00:00+00
+ 2001-10-26 04:00:00+00
+ 2001-10-27 04:00:00+00
+ 2001-10-28 04:00:00+00
+ 2001-10-29 05:00:00+00
+ 2001-10-30 05:00:00+00
+ 2001-10-31 05:00:00+00
+ 2001-11-01 05:00:00+00
+(11 rows)
 </programlisting>
   </para>
 
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index c266d0d02ed..aaadc68ae6a 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -69,6 +69,7 @@ typedef struct
 	TimestampTz finish;
 	Interval	step;
 	int			step_sign;
+	pg_tz	   *attimezone;
 } generate_series_timestamptz_fctx;
 
 
@@ -532,6 +533,21 @@ parse_sane_timezone(struct pg_tm *tm, text *zone)
 }
 
 /*
+ * Look up the requested timezone, returning a pg_tz struct.
+ *
+ * This is the same as DecodeTimezoneNameToTz, but starting with a text Datum.
+ */
+static pg_tz *
+lookup_timezone(text *zone)
+{
+	char		tzname[TZ_STRLEN_MAX + 1];
+
+	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+	return DecodeTimezoneNameToTz(tzname);
+}
+
+/*
  * make_timestamp_internal
  *		workhorse for make_timestamp and make_timestamptz
  */
@@ -2998,20 +3014,22 @@ timestamp_mi_interval(PG_FUNCTION_ARGS)
 }
 
 
-/* timestamptz_pl_interval()
- * Add an interval to a timestamp with time zone data type.
- * Note that interval has provisions for qualitative year/month
+/* timestamptz_pl_interval_internal()
+ * Add an interval to a timestamptz, in the given (or session) timezone.
+ *
+ * Note that interval has provisions for qualitative year/month and day
  *	units, so try to do the right thing with them.
  * To add a month, increment the month, and use the same day of month.
  * Then, if the next month has fewer days, set the day of month
  *	to the last day of month.
+ * To add a day, increment the mday, and use the same time of day.
  * Lastly, add in the "quantitative time".
  */
-Datum
-timestamptz_pl_interval(PG_FUNCTION_ARGS)
+static TimestampTz
+timestamptz_pl_interval_internal(TimestampTz timestamp,
+								 Interval *span,
+								 pg_tz *attimezone)
 {
-	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
-	Interval   *span = PG_GETARG_INTERVAL_P(1);
 	TimestampTz result;
 	int			tz;
 
@@ -3019,13 +3037,17 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 		result = timestamp;
 	else
 	{
+		/* 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;
 
-			if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+			if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, attimezone) != 0)
 				ereport(ERROR,
 						(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 						 errmsg("timestamp out of range")));
@@ -3046,7 +3068,7 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 			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]);
 
-			tz = DetermineTimeZoneOffset(tm, session_timezone);
+			tz = DetermineTimeZoneOffset(tm, attimezone);
 
 			if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
 				ereport(ERROR,
@@ -3061,7 +3083,7 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 			fsec_t		fsec;
 			int			julian;
 
-			if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+			if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, attimezone) != 0)
 				ereport(ERROR,
 						(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 						 errmsg("timestamp out of range")));
@@ -3070,7 +3092,7 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 			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, session_timezone);
+			tz = DetermineTimeZoneOffset(tm, attimezone);
 
 			if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
 				ereport(ERROR,
@@ -3088,25 +3110,71 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 		result = timestamp;
 	}
 
-	PG_RETURN_TIMESTAMP(result);
+	return result;
 }
 
-Datum
-timestamptz_mi_interval(PG_FUNCTION_ARGS)
+/* timestamptz_mi_interval_internal()
+ * As above, but subtract the interval.
+ */
+static TimestampTz
+timestamptz_mi_interval_internal(TimestampTz timestamp,
+								 Interval *span,
+								 pg_tz *attimezone)
 {
-	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
-	Interval   *span = PG_GETARG_INTERVAL_P(1);
 	Interval	tspan;
 
 	tspan.month = -span->month;
 	tspan.day = -span->day;
 	tspan.time = -span->time;
 
-	return DirectFunctionCall2(timestamptz_pl_interval,
-							   TimestampGetDatum(timestamp),
-							   PointerGetDatum(&tspan));
+	return timestamptz_pl_interval_internal(timestamp, &tspan, attimezone);
+}
+
+/* timestamptz_pl_interval()
+ * Add an interval to a timestamptz, in the session timezone.
+ */
+Datum
+timestamptz_pl_interval(PG_FUNCTION_ARGS)
+{
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
+	Interval   *span = PG_GETARG_INTERVAL_P(1);
+
+	PG_RETURN_TIMESTAMP(timestamptz_pl_interval_internal(timestamp, span, NULL));
 }
 
+Datum
+timestamptz_mi_interval(PG_FUNCTION_ARGS)
+{
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
+	Interval   *span = PG_GETARG_INTERVAL_P(1);
+
+	PG_RETURN_TIMESTAMP(timestamptz_mi_interval_internal(timestamp, span, NULL));
+}
+
+/* timestamptz_pl_interval_at_zone()
+ * Add an interval to a timestamptz, in the specified timezone.
+ */
+Datum
+timestamptz_pl_interval_at_zone(PG_FUNCTION_ARGS)
+{
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
+	Interval   *span = PG_GETARG_INTERVAL_P(1);
+	text	   *zone = PG_GETARG_TEXT_PP(2);
+	pg_tz	   *attimezone = lookup_timezone(zone);
+
+	PG_RETURN_TIMESTAMP(timestamptz_pl_interval_internal(timestamp, span, attimezone));
+}
+
+Datum
+timestamptz_mi_interval_at_zone(PG_FUNCTION_ARGS)
+{
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
+	Interval   *span = PG_GETARG_INTERVAL_P(1);
+	text	   *zone = PG_GETARG_TEXT_PP(2);
+	pg_tz	   *attimezone = lookup_timezone(zone);
+
+	PG_RETURN_TIMESTAMP(timestamptz_mi_interval_internal(timestamp, span, attimezone));
+}
 
 Datum
 interval_um(PG_FUNCTION_ARGS)
@@ -3396,13 +3464,9 @@ in_range_timestamptz_interval(PG_FUNCTION_ARGS)
 
 	/* We don't currently bother to avoid overflow hazards here */
 	if (sub)
-		sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval,
-													  TimestampTzGetDatum(base),
-													  IntervalPGetDatum(offset)));
+		sum = timestamptz_mi_interval_internal(base, offset, NULL);
 	else
-		sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
-													  TimestampTzGetDatum(base),
-													  IntervalPGetDatum(offset)));
+		sum = timestamptz_pl_interval_internal(base, offset, NULL);
 
 	if (less)
 		PG_RETURN_BOOL(val <= sum);
@@ -4284,7 +4348,6 @@ timestamptz_trunc_zone(PG_FUNCTION_ARGS)
 	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
 	text	   *zone = PG_GETARG_TEXT_PP(2);
 	TimestampTz result;
-	char		tzname[TZ_STRLEN_MAX + 1];
 	pg_tz	   *tzp;
 
 	/*
@@ -4297,9 +4360,7 @@ timestamptz_trunc_zone(PG_FUNCTION_ARGS)
 	/*
 	 * Look up the requested timezone.
 	 */
-	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
-
-	tzp = DecodeTimezoneNameToTz(tzname);
+	tzp = lookup_timezone(zone);
 
 	result = timestamptz_trunc_internal(units, timestamp, tzp);
 
@@ -5776,10 +5837,11 @@ generate_series_timestamp(PG_FUNCTION_ARGS)
 }
 
 /* generate_series_timestamptz()
- * Generate the set of timestamps from start to finish by step
+ * Generate the set of timestamps from start to finish by step,
+ * doing arithmetic in the specified or session timezone.
  */
-Datum
-generate_series_timestamptz(PG_FUNCTION_ARGS)
+static Datum
+generate_series_timestamptz_internal(FunctionCallInfo fcinfo)
 {
 	FuncCallContext *funcctx;
 	generate_series_timestamptz_fctx *fctx;
@@ -5791,6 +5853,7 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
 		TimestampTz start = PG_GETARG_TIMESTAMPTZ(0);
 		TimestampTz finish = PG_GETARG_TIMESTAMPTZ(1);
 		Interval   *step = PG_GETARG_INTERVAL_P(2);
+		text	   *zone = (PG_NARGS() == 4) ? PG_GETARG_TEXT_PP(3) : NULL;
 		MemoryContext oldcontext;
 		const Interval interval_zero = {0};
 
@@ -5813,6 +5876,7 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
 		fctx->current = start;
 		fctx->finish = finish;
 		fctx->step = *step;
+		fctx->attimezone = zone ? lookup_timezone(zone) : session_timezone;
 
 		/* Determine sign of the interval */
 		fctx->step_sign = interval_cmp_internal(&fctx->step, &interval_zero);
@@ -5840,9 +5904,9 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
 		timestamp_cmp_internal(result, fctx->finish) >= 0)
 	{
 		/* increment current in preparation for next iteration */
-		fctx->current = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
-																TimestampTzGetDatum(fctx->current),
-																PointerGetDatum(&fctx->step)));
+		fctx->current = timestamptz_pl_interval_internal(fctx->current,
+														 &fctx->step,
+														 fctx->attimezone);
 
 		/* do when there is more left to send */
 		SRF_RETURN_NEXT(funcctx, TimestampTzGetDatum(result));
@@ -5853,3 +5917,15 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
 		SRF_RETURN_DONE(funcctx);
 	}
 }
+
+Datum
+generate_series_timestamptz(PG_FUNCTION_ARGS)
+{
+	return generate_series_timestamptz_internal(fcinfo);
+}
+
+Datum
+generate_series_timestamptz_at_zone(PG_FUNCTION_ARGS)
+{
+	return generate_series_timestamptz_internal(fcinfo);
+}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index b2eed22d46c..e94528a7c71 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202303151
+#define CATALOG_VERSION_NO	202303181
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fbc4aade494..5cf87aeb2c4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2426,10 +2426,26 @@
   proname => 'timestamptz_pl_interval', provolatile => 's',
   prorettype => 'timestamptz', proargtypes => 'timestamptz interval',
   prosrc => 'timestamptz_pl_interval' },
+{ oid => '8800', descr => 'add interval to timestamp with time zone',
+  proname => 'date_add', provolatile => 's', prorettype => 'timestamptz',
+  proargtypes => 'timestamptz interval', prosrc => 'timestamptz_pl_interval' },
+{ oid => '8801',
+  descr => 'add interval to timestamp with time zone in specified time zone',
+  proname => 'date_add', prorettype => 'timestamptz',
+  proargtypes => 'timestamptz interval text',
+  prosrc => 'timestamptz_pl_interval_at_zone' },
 { oid => '1190',
   proname => 'timestamptz_mi_interval', provolatile => 's',
   prorettype => 'timestamptz', proargtypes => 'timestamptz interval',
   prosrc => 'timestamptz_mi_interval' },
+{ oid => '8802', descr => 'subtract interval from timestamp with time zone',
+  proname => 'date_subtract', provolatile => 's', prorettype => 'timestamptz',
+  proargtypes => 'timestamptz interval', prosrc => 'timestamptz_mi_interval' },
+{ oid => '8803',
+  descr => 'subtract interval from timestamp with time zone in specified time zone',
+  proname => 'date_subtract', prorettype => 'timestamptz',
+  proargtypes => 'timestamptz interval text',
+  prosrc => 'timestamptz_mi_interval_at_zone' },
 { oid => '1195', descr => 'smaller of two',
   proname => 'timestamptz_smaller', prorettype => 'timestamptz',
   proargtypes => 'timestamptz timestamptz', prosrc => 'timestamp_smaller' },
@@ -8252,6 +8268,11 @@
   provolatile => 's', prorettype => 'timestamptz',
   proargtypes => 'timestamptz timestamptz interval',
   prosrc => 'generate_series_timestamptz' },
+{ oid => '8804', descr => 'non-persistent series generator',
+  proname => 'generate_series', prorows => '1000', proretset => 't',
+  prorettype => 'timestamptz',
+  proargtypes => 'timestamptz timestamptz interval text',
+  prosrc => 'generate_series_timestamptz_at_zone' },
 
 # boolean aggregates
 { oid => '2515', descr => 'aggregate transition function',
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 91d7c1f5cc2..0dd2fe2c82d 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2468,6 +2468,60 @@ select * from generate_series('2020-01-01 00:00'::timestamptz,
                               '2020-01-02 03:00'::timestamptz,
                               '0 hour'::interval);
 ERROR:  step size cannot equal zero
+-- Interval crossing time shift for Europe/Warsaw timezone (with DST)
+SET TimeZone to 'UTC';
+SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
+                '1 day'::interval);
+           date_add           
+------------------------------
+ Sun Oct 30 23:00:00 2022 UTC
+(1 row)
+
+SELECT date_add('2021-10-31 00:00:00+02'::timestamptz,
+                '1 day'::interval,
+                'Europe/Warsaw');
+           date_add           
+------------------------------
+ Sun Oct 31 23:00:00 2021 UTC
+(1 row)
+
+SELECT date_subtract('2022-10-30 00:00:00+01'::timestamptz,
+                     '1 day'::interval);
+        date_subtract         
+------------------------------
+ Fri Oct 28 23:00:00 2022 UTC
+(1 row)
+
+SELECT date_subtract('2021-10-31 00:00:00+02'::timestamptz,
+                     '1 day'::interval,
+                     'Europe/Warsaw');
+        date_subtract         
+------------------------------
+ Fri Oct 29 22:00:00 2021 UTC
+(1 row)
+
+SELECT * FROM generate_series('2021-12-31 23:00:00+00'::timestamptz,
+                              '2020-12-31 23:00:00+00'::timestamptz,
+                              '-1 month'::interval,
+                              'Europe/Warsaw');
+       generate_series        
+------------------------------
+ Fri Dec 31 23:00:00 2021 UTC
+ Tue Nov 30 23:00:00 2021 UTC
+ Sun Oct 31 23:00:00 2021 UTC
+ Thu Sep 30 22:00:00 2021 UTC
+ Tue Aug 31 22:00:00 2021 UTC
+ Sat Jul 31 22:00:00 2021 UTC
+ Wed Jun 30 22:00:00 2021 UTC
+ Mon May 31 22:00:00 2021 UTC
+ Fri Apr 30 22:00:00 2021 UTC
+ Wed Mar 31 22:00:00 2021 UTC
+ Sun Feb 28 23:00:00 2021 UTC
+ Sun Jan 31 23:00:00 2021 UTC
+ Thu Dec 31 23:00:00 2020 UTC
+(13 rows)
+
+RESET TimeZone;
 --
 -- Test behavior with a dynamic (time-varying) timezone abbreviation.
 -- These tests rely on the knowledge that MSK (Europe/Moscow standard time)
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index ae9ee4b56a2..69b36d04202 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -459,6 +459,25 @@ select * from generate_series('2020-01-01 00:00'::timestamptz,
                               '2020-01-02 03:00'::timestamptz,
                               '0 hour'::interval);
 
+-- Interval crossing time shift for Europe/Warsaw timezone (with DST)
+SET TimeZone to 'UTC';
+
+SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
+                '1 day'::interval);
+SELECT date_add('2021-10-31 00:00:00+02'::timestamptz,
+                '1 day'::interval,
+                'Europe/Warsaw');
+SELECT date_subtract('2022-10-30 00:00:00+01'::timestamptz,
+                     '1 day'::interval);
+SELECT date_subtract('2021-10-31 00:00:00+02'::timestamptz,
+                     '1 day'::interval,
+                     'Europe/Warsaw');
+SELECT * FROM generate_series('2021-12-31 23:00:00+00'::timestamptz,
+                              '2020-12-31 23:00:00+00'::timestamptz,
+                              '-1 month'::interval,
+                              'Europe/Warsaw');
+RESET TimeZone;
+
 --
 -- Test behavior with a dynamic (time-varying) timezone abbreviation.
 -- These tests rely on the knowledge that MSK (Europe/Moscow standard time)