aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/timestamp.c146
-rw-r--r--src/include/catalog/catversion.h2
-rw-r--r--src/include/catalog/pg_proc.dat21
-rw-r--r--src/test/regress/expected/timestamptz.out54
-rw-r--r--src/test/regress/sql/timestamptz.sql19
5 files changed, 206 insertions, 36 deletions
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)
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-22 23:31:53 +0000