Tom Lane wrote on 04.07.2022 00:31:
=?UTF-8?Q?Przemys=c5=82aw_Sztoch?= <przemys...@sztoch.pl> writes:
I have problem with this:
-- Considering only built-in procs (prolang = 12), look for multiple uses
-- of the same internal function (ie, matching prosrc fields). It's OK to
-- have several entries with different pronames for the same internal
function,
-- but conflicts in the number of arguments and other critical items should
-- be complained of. (We don't check data types here; see next query.)
It's telling you you're violating project style. Don't make multiple
pg_proc entries point at the same C function and then use PG_NARGS
to disambiguate; instead point at two separate functions. The functions
can share code at the next level down, if they want. (Just looking
at the patch, though, I wonder if sharing code is really beneficial
in this case. It seems quite messy, and I wouldn't be surprised
if it hurts performance in the existing case.)
You also need to expend some more effort on refactoring code, to
eliminate silliness like looking up the timezone name each time
through the SRF. That's got to be pretty awful performance-wise.
regards, tom lane
Thx. Code is refactored. It is better, now.
--
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..ee1dee7c84 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;
@@ -547,6 +548,48 @@ parse_sane_timezone(struct pg_tm *tm, text *zone)
return tz;
}
+static pg_tz *
+lookup_timezone(text *zone)
+{
+ 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));
+
+ /* DecodeTimezoneAbbrev requires lowercase input */
+ lowzone = downcase_truncate_identifier(tzname,
+
strlen(tzname),
+
false);
+
+ type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+ if (type == TZ || type == DTZ)
+ {
+ /* fixed-offset abbreviation, get a pg_tz descriptor for that */
+ tzp = pg_tzset_offset(-val);
+ }
+ else if (type == DYNTZ)
+ {
+ /* 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_INVALID_PARAMETER_VALUE),
+ errmsg("time zone \"%s\" not
recognized", tzname)));
+ }
+
+ return tzp;
+}
+
/*
* make_timestamp_internal
* workhorse for make_timestamp and make_timestamptz
@@ -2989,97 +3032,107 @@ 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
+/*
+ * 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".
*/
+static TimestampTz
+timestamptz_pl_interval_internal(TimestampTz timestamp, Interval *span, pg_tz
*attimezone)
+{
+ int tz;
+
+ /* 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, attimezone)
!= 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;
+ }
+
+ /* 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]);
+
+ 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")));
+ }
+
+ if (span->day != 0)
+ {
+ struct pg_tm tt,
+ *tm = &tt;
+ fsec_t fsec;
+ int julian;
+
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, attimezone)
!= 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")));
+ }
+
+ timestamp += span->time;
+
+ if (!IS_VALID_TIMESTAMP(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ return timestamp;
+}
+
+
+/* timestamptz_pl_interval()
+ * Add an interval to a timestamp with time zone data type in session timezone.
+ */
Datum
timestamptz_pl_interval(PG_FUNCTION_ARGS)
{
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
Interval *span = PG_GETARG_INTERVAL_P(1);
- TimestampTz result;
- int tz;
if (TIMESTAMP_NOT_FINITE(timestamp))
- result = timestamp;
- else
- {
- if (span->month != 0)
- {
- struct pg_tm tt,
- *tm = &tt;
- fsec_t fsec;
+ PG_RETURN_TIMESTAMP(timestamp);
- if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL)
!= 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;
- }
-
- /* 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]);
-
- tz = DetermineTimeZoneOffset(tm, session_timezone);
-
- if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
- }
-
- 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, session_timezone);
-
- if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
- }
-
- timestamp += span->time;
-
- if (!IS_VALID_TIMESTAMP(timestamp))
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of range")));
-
- result = timestamp;
- }
-
- PG_RETURN_TIMESTAMP(result);
+ PG_RETURN_TIMESTAMP(timestamptz_pl_interval_internal(timestamp, span,
NULL));
}
Datum
@@ -3099,6 +3152,27 @@ timestamptz_mi_interval(PG_FUNCTION_ARGS)
}
+/* timestamptz_add_zone()
+ * Add an interval to a timestamp with time zone data type in specified
timezone.
+ */
+Datum
+timestamptz_add_zone(PG_FUNCTION_ARGS)
+{
+ TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
+ Interval *span = PG_GETARG_INTERVAL_P(1);
+ text *zone;
+ pg_tz *attimezone;
+
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMP(timestamp);
+
+ zone = PG_GETARG_TEXT_PP(2);
+ attimezone = lookup_timezone(zone);
+
+ PG_RETURN_TIMESTAMP(timestamptz_pl_interval_internal(timestamp, span,
attimezone));
+}
+
+
Datum
interval_um(PG_FUNCTION_ARGS)
{
@@ -4275,10 +4349,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];
- char *lowzone;
- int type,
- val;
pg_tz *tzp;
/*
@@ -4288,42 +4358,221 @@ timestamptz_trunc_zone(PG_FUNCTION_ARGS)
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_TIMESTAMP(timestamp);
- /*
- * Look up the requested timezone (see notes in timestamptz_zone()).
- */
- text_to_cstring_buffer(zone, tzname, sizeof(tzname));
-
- /* DecodeTimezoneAbbrev requires lowercase input */
- lowzone = downcase_truncate_identifier(tzname,
-
strlen(tzname),
-
false);
-
- type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
-
- if (type == TZ || type == DTZ)
- {
- /* fixed-offset abbreviation, get a pg_tz descriptor for that */
- tzp = pg_tzset_offset(-val);
- }
- else if (type == DYNTZ)
- {
- /* 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_INVALID_PARAMETER_VALUE),
- errmsg("time zone \"%s\" not
recognized", tzname)));
- }
+ tzp = lookup_timezone(zone);
result = timestamptz_trunc_internal(units, timestamp, tzp);
PG_RETURN_TIMESTAMPTZ(result);
}
+/*
+ * Common code for timestamptz_trunc_int() and timestamptz_trunc_int_zone().
+ *
+ * tzp identifies the zone to truncate with respect to. We assume
+ * infinite timestamps have already been rejected.
+ */
+static TimestampTz
+timestamptz_trunc_int_internal(Interval *interval, TimestampTz timestamp,
pg_tz *tzp)
+{
+ TimestampTz result;
+ int tz;
+ int interval_parts = 0;
+ bool bad_interval = false;
+ bool redotz = false;
+ fsec_t fsec;
+ struct pg_tm tt,
+ *tm = &tt;
+
+ if (interval->month != 0)
+ {
+ interval_parts++;
+ /* 1200 = hundred years */
+ if ((1200/interval->month) * interval->month != 1200)
+ bad_interval = true;
+ }
+ if (interval->day != 0)
+ {
+ interval_parts++;
+ if (interval->day != 1 && interval->day != 7)
+ bad_interval = true;
+ }
+ if (interval->time != 0)
+ {
+ interval_parts++;
+ if (interval->time > USECS_PER_SEC)
+ {
+ if ((interval->time % USECS_PER_SEC) != 0)
+ bad_interval = true;
+ if ((USECS_PER_DAY/interval->time) * interval->time !=
USECS_PER_DAY)
+ bad_interval = true;
+ }
+ else if (interval->time < USECS_PER_SEC &&
(USECS_PER_SEC/interval->time) * interval->time != USECS_PER_SEC)
+ bad_interval = true;
+ }
+ if (interval_parts != 1 || bad_interval)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("interval has to be a divisor of a day,
week or century.")));
+ return 0;
+ }
+
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ if (interval->month != 0)
+ {
+ int months;
+ months = (tm->tm_year - 1) * 12 + tm->tm_mon - 1;
+ months -= months % interval->month;
+ tm->tm_year = (months / 12) + 1;
+ tm->tm_mon = (months % 12) + 1;
+ tm->tm_mday = 1;
+ tm->tm_hour = 0;
+ tm->tm_min = 0;
+ tm->tm_sec = 0;
+ fsec = 0;
+ redotz = true;
+ }
+ else if (interval->day == 7)
+ {
+ int woy;
+ woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
+
+ /*
+ * If it is week 52/53 and the month is January, then the
+ * week must belong to the previous year. Also, some
+ * December dates belong to the next year.
+ */
+ if (woy >= 52 && tm->tm_mon == 1)
+ --tm->tm_year;
+ if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
+ ++tm->tm_year;
+ isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon),
&(tm->tm_mday));
+ tm->tm_hour = 0;
+ tm->tm_min = 0;
+ tm->tm_sec = 0;
+ fsec = 0;
+ redotz = true;
+ }
+ else if (interval->day == 1)
+ {
+ tm->tm_hour = 0;
+ tm->tm_min = 0;
+ tm->tm_sec = 0;
+ fsec = 0;
+ redotz = true; /* for all cases > HOUR */
+ }
+ else if (interval->time > USECS_PER_SEC)
+ {
+ int seconds;
+ seconds = tm->tm_hour * 3600 + tm->tm_min * 60 + tm->tm_sec;
+ seconds -= seconds % (interval->time / USECS_PER_SEC);
+ tm->tm_hour = seconds / 3600;
+ tm->tm_min = (seconds / 60) % 60;
+ tm->tm_sec = seconds % 60;
+ fsec = 0;
+ redotz = (interval->time > USECS_PER_HOUR);
+ }
+ else if (interval->time == USECS_PER_SEC)
+ fsec = 0;
+ else if (interval->time > 0)
+ fsec -= fsec % interval->time;
+
+ if (redotz)
+ {
+ int alt_tz = DetermineTimeZoneOffset(tm, tzp);
+ int seconds;
+
+ if (tm2timestamp(tm, fsec, &alt_tz, &result) != 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ if (result <= timestamp)
+ return result;
+
+ /* If the beginning of the bucket is from the future, we have a
DST case. We have to append "double meaning" hour to previous bucket. */
+ if (interval->time <= USECS_PER_HOUR)
+ {
+ /* This foul case is possible only for intervals
greater than an hour and less than a day. */
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("timestamp dual meaning problem
(DST case)")));
+ return result;
+ }
+
+ timestamp -= USECS_PER_HOUR;
+
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ seconds = tm->tm_hour * 3600 + tm->tm_min * 60 + tm->tm_sec;
+ seconds -= seconds % (interval->time / USECS_PER_SEC);
+ tm->tm_hour = seconds / 3600;
+ tm->tm_min = (seconds / 60) % 60;
+ tm->tm_sec = seconds % 60;
+ fsec = 0;
+
+ tz = DetermineTimeZoneOffset(tm, tzp);
+ }
+
+ if (tm2timestamp(tm, fsec, &tz, &result) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ return result;
+}
+
+/* timestamptz_trunc_int()
+ * Truncate timestamptz to specified interval in session timezone.
+ */
+Datum
+timestamptz_trunc_int(PG_FUNCTION_ARGS)
+{
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+ TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+ TimestampTz result;
+
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMPTZ(timestamp);
+
+ result = timestamptz_trunc_int_internal(interval, timestamp,
session_timezone);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/* timestamptz_trunc_zone()
+ * Truncate timestamptz to specified interval in specified timezone.
+ */
+Datum
+timestamptz_trunc_int_zone(PG_FUNCTION_ARGS)
+{
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+ TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+ text *zone = PG_GETARG_TEXT_PP(2);
+ TimestampTz result;
+ pg_tz *tzp;
+
+ /*
+ * timestamptz_zone() doesn't look up the zone for infinite inputs, so
we
+ * don't do so here either.
+ */
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMP(timestamp);
+
+ tzp = lookup_timezone(zone);
+
+ result = timestamptz_trunc_int_internal(interval, timestamp, tzp);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
/* interval_trunc()
* Extract specified field from interval.
*/
@@ -5906,9 +6155,88 @@ 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, NULL);
+
+ /* do when there is more left to send */
+ SRF_RETURN_NEXT(funcctx, TimestampTzGetDatum(result));
+ }
+ else
+ {
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+/* generate_series_timestamptz_zone()
+ * Generate the set of timestamps from start to finish by step in specified
timezone.
+ */
+Datum
+generate_series_timestamptz_zone(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ generate_series_timestamptz_fctx *fctx;
+ TimestampTz result;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ TimestampTz start = PG_GETARG_TIMESTAMPTZ(0);
+ TimestampTz finish = PG_GETARG_TIMESTAMPTZ(1);
+ Interval *step = PG_GETARG_INTERVAL_P(2);
+ MemoryContext oldcontext;
+ Interval interval_zero;
+ text *zone;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function
calls
+ */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* allocate memory for user context */
+ fctx = (generate_series_timestamptz_fctx *)
+ palloc(sizeof(generate_series_timestamptz_fctx));
+
+ /*
+ * Use fctx to keep state from call to call. Seed current with
the
+ * original start value
+ */
+ fctx->current = start;
+ fctx->finish = finish;
+ fctx->step = *step;
+
+ /* Determine sign of the interval */
+ MemSet(&interval_zero, 0, sizeof(Interval));
+ fctx->step_sign = interval_cmp_internal(&fctx->step,
&interval_zero);
+
+ if (fctx->step_sign == 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot equal
zero")));
+
+ zone = PG_GETARG_TEXT_PP(3);
+ fctx->attimezone = lookup_timezone(zone);
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /*
+ * get the saved state and use current as the result for this iteration
+ */
+ fctx = funcctx->user_fctx;
+ result = fctx->current;
+
+ if (fctx->step_sign > 0 ?
+ timestamp_cmp_internal(result, fctx->finish) <= 0 :
+ timestamp_cmp_internal(result, fctx->finish) >= 0)
+ {
+ /* increment current in preparation for next iteration */
+ 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));
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a77b293723..3c0edc3076 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2429,11 +2429,19 @@
proargtypes => 'text timestamptz', prosrc => 'timestamptz_trunc' },
{ oid => '1284',
descr => 'truncate timestamp with time zone to specified units in specified
time zone',
- proname => 'date_trunc', provolatile => 's', prorettype => 'timestamptz',
+ proname => 'date_trunc', prorettype => 'timestamptz',
proargtypes => 'text timestamptz text', prosrc => 'timestamptz_trunc_zone' },
{ oid => '1218', descr => 'truncate interval to specified units',
proname => 'date_trunc', prorettype => 'interval',
proargtypes => 'text interval', prosrc => 'interval_trunc' },
+{ oid => '8802',
+ descr => 'truncate timestamp with time zone to specified interval',
+ proname => 'date_trunc', provolatile => 's', prorettype => 'timestamptz',
+ proargtypes => 'interval timestamptz', prosrc => 'timestamptz_trunc_int' },
+{ oid => '8803',
+ descr => 'truncate timestamp with time zone to specified interval in
specified time zone',
+ proname => 'date_trunc', prorettype => 'timestamptz',
+ proargtypes => 'interval timestamptz text', prosrc =>
'timestamptz_trunc_int_zone' },
{ oid => '1219', descr => 'increment',
proname => 'int8inc', prorettype => 'int8', proargtypes => 'int8',
@@ -8137,6 +8145,16 @@
provolatile => 's', prorettype => 'timestamptz',
proargtypes => 'timestamptz timestamptz interval',
prosrc => 'generate_series_timestamptz' },
+{ oid => '8801', descr => 'non-persistent series generator',
+ proname => 'generate_series', prorows => '1000', proretset => 't',
+ prorettype => 'timestamptz',
+ proargtypes => 'timestamptz timestamptz interval text',
+ prosrc => 'generate_series_timestamptz_zone' },
+{ oid => '8800',
+ descr => 'add interval to timestamp with time zone in specified time zone',
+ proname => 'date_add',
+ prorettype => 'timestamptz', proargtypes => 'timestamptz interval text',
+ prosrc => 'timestamptz_add_zone' },
# boolean aggregates
{ oid => '2515', descr => 'aggregate transition function',
@@ -11885,5 +11903,4 @@
proname => 'brin_minmax_multi_summary_send', provolatile => 's',
prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
prosrc => 'brin_minmax_multi_summary_send' },
-
]
diff --git a/src/test/regress/expected/timestamptz.out
b/src/test/regress/expected/timestamptz.out
index eba84191d3..53dde828f1 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -658,7 +658,7 @@ SELECT d1 - timestamp with time zone '1997-01-02' AS diff
@ 1460 days 17 hours 32 mins 1 sec
(56 rows)
-SELECT date_trunc( 'week', timestamp with time zone '2004-02-29
15:44:17.71393' ) AS week_trunc;
+SELECT date_trunc('week', timestamp with time zone '2004-02-29 15:44:17.71393'
) AS week_trunc;
week_trunc
------------------------------
Mon Feb 23 00:00:00 2004 PST
@@ -682,11 +682,26 @@ SELECT date_trunc('day', timestamp with time zone
'2001-02-16 20:38:40+00', 'VET
Thu Feb 15 20:00:00 2001 PST
(1 row)
+-- errors
+SELECT date_trunc('1 month 7 day'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval1;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('1 month 01:00:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval2;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('1 day 00:30:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval3;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('7 month'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval4;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('3 day'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval5;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('00:23:00'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval6;
+ERROR: interval has to be a divisor of a day, week or century.
-- verify date_bin behaves the same as date_trunc for relevant intervals
SELECT
str,
interval,
- date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts,
timestamp with time zone '2001-01-01+11') AS equal
+ timezone,
+ date_trunc(str, ts, timezone) = date_bin(interval::interval, ts,
timezone(timezone, '2001-01-01 00:00'::timestamp)) AS equal1,
+ date_trunc(str, ts, timezone) = date_trunc(interval::interval, ts, timezone)
AS equal2
FROM (
VALUES
('day', '1 d'),
@@ -696,16 +711,33 @@ FROM (
('millisecond', '1 ms'),
('microsecond', '1 us')
) intervals (str, interval),
+(VALUES
+ ('Australia/Sydney'),
+ ('Europe/Warsaw'),
+ ('Europe/London')
+) timezone (timezone),
(VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts);
- str | interval | equal
--------------+----------+-------
- day | 1 d | t
- hour | 1 h | t
- minute | 1 m | t
- second | 1 s | t
- millisecond | 1 ms | t
- microsecond | 1 us | t
-(6 rows)
+ str | interval | timezone | equal1 | equal2
+-------------+----------+------------------+--------+--------
+ day | 1 d | Australia/Sydney | t | t
+ day | 1 d | Europe/Warsaw | t | t
+ day | 1 d | Europe/London | t | t
+ hour | 1 h | Australia/Sydney | t | t
+ hour | 1 h | Europe/Warsaw | t | t
+ hour | 1 h | Europe/London | t | t
+ minute | 1 m | Australia/Sydney | t | t
+ minute | 1 m | Europe/Warsaw | t | t
+ minute | 1 m | Europe/London | t | t
+ second | 1 s | Australia/Sydney | t | t
+ second | 1 s | Europe/Warsaw | t | t
+ second | 1 s | Europe/London | t | t
+ millisecond | 1 ms | Australia/Sydney | t | t
+ millisecond | 1 ms | Europe/Warsaw | t | t
+ millisecond | 1 ms | Europe/London | t | t
+ microsecond | 1 us | Australia/Sydney | t | t
+ microsecond | 1 us | Europe/Warsaw | t | t
+ microsecond | 1 us | Europe/London | t | t
+(18 rows)
-- bin timestamps into arbitrary intervals
SELECT
@@ -2363,7 +2395,7 @@ SELECT make_timestamptz(2014, 12, 10, 10, 10, 10,
'PST8PDT');
RESET TimeZone;
-- generate_series for timestamptz
-select * from generate_series('2020-01-01 00:00'::timestamptz,
+SELECT * FROM generate_series('2020-01-01 00:00'::timestamptz,
'2020-01-02 03:00'::timestamptz,
'1 hour'::interval);
generate_series
@@ -2418,10 +2450,49 @@ select generate_series('2022-01-01 00:00'::timestamptz,
(10 rows)
-- errors
-select * from generate_series('2020-01-01 00:00'::timestamptz,
+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('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_add('2022-10-30 00:00:00+01'::timestamptz,
+ '1 day'::interval,
+ 'Europe/London');
+ date_add
+------------------------------
+ Mon Oct 31 00:00:00 2022 UTC
+(1 row)
+
+SELECT * FROM generate_series('2020-12-31 23:00:00+00'::timestamptz,
+ '2021-12-31 23:00:00+00'::timestamptz,
+ '1 month'::interval,
+ 'Europe/Warsaw');
+ generate_series
+------------------------------
+ Thu Dec 31 23:00:00 2020 UTC
+ Sun Jan 31 23:00:00 2021 UTC
+ Sun Feb 28 23:00:00 2021 UTC
+ Wed Mar 31 22:00:00 2021 UTC
+ Fri Apr 30 22:00:00 2021 UTC
+ Mon May 31 22:00:00 2021 UTC
+ Wed Jun 30 22:00:00 2021 UTC
+ Sat Jul 31 22:00:00 2021 UTC
+ Tue Aug 31 22:00:00 2021 UTC
+ Thu Sep 30 22:00:00 2021 UTC
+ Sun Oct 31 23:00:00 2021 UTC
+ Tue Nov 30 23:00:00 2021 UTC
+ Fri Dec 31 23:00:00 2021 UTC
+(13 rows)
+
--
-- 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 a107abc5a4..38f7360a2c 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -190,17 +190,27 @@ SELECT d1 FROM TIMESTAMPTZ_TBL
SELECT d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-SELECT date_trunc( 'week', timestamp with time zone '2004-02-29
15:44:17.71393' ) AS week_trunc;
+SELECT date_trunc('week', timestamp with time zone '2004-02-29 15:44:17.71393'
) AS week_trunc;
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00',
'Australia/Sydney') as sydney_trunc; -- zone name
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00',
'GMT') as gmt_trunc; -- fixed-offset abbreviation
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00',
'VET') as vet_trunc; -- variable-offset abbreviation
+-- errors
+SELECT date_trunc('1 month 7 day'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval1;
+SELECT date_trunc('1 month 01:00:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval2;
+SELECT date_trunc('1 day 00:30:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval3;
+SELECT date_trunc('7 month'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval4;
+SELECT date_trunc('3 day'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval5;
+SELECT date_trunc('00:23:00'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval6;
+
-- verify date_bin behaves the same as date_trunc for relevant intervals
SELECT
str,
interval,
- date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts,
timestamp with time zone '2001-01-01+11') AS equal
+ timezone,
+ date_trunc(str, ts, timezone) = date_bin(interval::interval, ts,
timezone(timezone, '2001-01-01 00:00'::timestamp)) AS equal1,
+ date_trunc(str, ts, timezone) = date_trunc(interval::interval, ts, timezone)
AS equal2
FROM (
VALUES
('day', '1 d'),
@@ -210,6 +220,11 @@ FROM (
('millisecond', '1 ms'),
('microsecond', '1 us')
) intervals (str, interval),
+(VALUES
+ ('Australia/Sydney'),
+ ('Europe/Warsaw'),
+ ('Europe/London')
+) timezone (timezone),
(VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts);
-- bin timestamps into arbitrary intervals
@@ -433,7 +448,7 @@ SELECT make_timestamptz(2014, 12, 10, 10, 10, 10,
'PST8PDT');
RESET TimeZone;
-- generate_series for timestamptz
-select * from generate_series('2020-01-01 00:00'::timestamptz,
+SELECT * FROM generate_series('2020-01-01 00:00'::timestamptz,
'2020-01-02 03:00'::timestamptz,
'1 hour'::interval);
-- the LIMIT should allow this to terminate in a reasonable amount of time
@@ -442,10 +457,23 @@ select generate_series('2022-01-01 00:00'::timestamptz,
'infinity'::timestamptz,
'1 month'::interval) limit 10;
-- errors
-select * from generate_series('2020-01-01 00:00'::timestamptz,
+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('2021-10-31 00:00:00+02'::timestamptz,
+ '1 day'::interval,
+ 'Europe/Warsaw');
+SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
+ '1 day'::interval,
+ 'Europe/London');
+SELECT * FROM generate_series('2020-12-31 23:00:00+00'::timestamptz,
+ '2021-12-31 23:00:00+00'::timestamptz,
+ '1 month'::interval,
+ 'Europe/Warsaw');
--
-- Test behavior with a dynamic (time-varying) timezone abbreviation.
-- These tests rely on the knowledge that MSK (Europe/Moscow standard time)
