Gurjeet Singh wrote on 01.07.2022 06:35:
On Tue, Jun 21, 2022 at 7:56 AM Przemysław Sztoch <przemys...@sztoch.pl> wrote:
Please give me feedback on how to properly store the timezone name in the
function context structure. I can't finish my work without it.
The way I see it, I don't think you need to store the tz-name in the
function context structure, like you're currently doing. I think you
can remove the additional member from the
generate_series_timestamptz_fctx struct, and refactor your code in
generate_series_timestamptz() to work without it.; you seem to be
using the tzname member almost as a boolean flag, because the actual
value you pass to DFCall3() can be calculated without first storing
anything in the struct.
Do I understand correctly that functions that return SET are executed
multiple times?
Is access to arguments available all the time?
I thought PG_GETARG_ could only be used when SRF_IS_FIRSTCALL () is true
- was I right or wrong?
Can you please explain why you chose to remove the provolatile
attribute from the existing entry of date_trunc in pg_proc.dat.
I believe it was a mistake in PG code.
All timestamptz functions must be STABLE as they depend on the current:
SHOW timezone.
If new functions are created that pass the zone as a parameter, they
become IMMUTABLE.
FIrst date_trunc function implementaion was without time zone parameter
and someone who
added second variant (with timezone as parameter) copied the definition
without removing the STABLE flag.
It seems like you've picked/reused code from neighboring functions
(e.g. from timestamptz_trunc_zone()). Can you please see if you can
turn such code into a function, and call the function, instead of
copying it.
Ok. Changed.
Also, according to the comment at the top of pg_proc.dat,
# Once upon a time these entries were ordered by OID. Lately it's often
# been the custom to insert new entries adjacent to related older entries.
So instead of adding your entries at the bottom of the file, please
each entry closer to an existing entry that's relevant to it.
Ok. Changed.
Some regression tests has been added.
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.)
-- Note: ignore aggregate functions here, since they all point to the same
-- dummy built-in function.
SELECT p1.oid, p1.proname, p2.oid, p2.proname (...):
oid | proname | oid | proname
------+-------------------------+------+-----------------
1189 | timestamptz_pl_interval | 8800 | date_add
939 | generate_series | 8801 | generate_series
(2 rows)
--
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..415d91dfce 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;
+ char tzname[TZ_STRLEN_MAX + 1];
} 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
@@ -3003,83 +3046,89 @@ 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
+ PG_RETURN_TIMESTAMP(timestamp);
+
+ if (PG_NARGS() > 2)
{
- if (span->month != 0)
- {
- struct pg_tm tt,
- *tm = &tt;
- fsec_t fsec;
+ text *zone = PG_GETARG_TEXT_PP(2);
+ attimezone = lookup_timezone(zone);
+ }
- if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL)
!= 0)
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
+ /* 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;
- 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))
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, attimezone)
!= 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
- result = 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;
+ }
+
+ /* 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")));
}
- PG_RETURN_TIMESTAMP(result);
+ 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")));
+
+ PG_RETURN_TIMESTAMP(timestamp);
}
Datum
@@ -4275,10 +4324,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 +4333,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.
*/
@@ -5888,6 +6112,15 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("step size cannot equal
zero")));
+ if (PG_NARGS() > 3)
+ {
+ text *zone = PG_GETARG_TEXT_PP(3);
+ text_to_cstring_buffer(zone, fctx->tzname,
sizeof(fctx->tzname));
+ }
+ else
+ {
+ fctx->tzname[0] = 0;
+ }
funcctx->user_fctx = fctx;
MemoryContextSwitchTo(oldcontext);
}
@@ -5906,9 +6139,20 @@ 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)));
+ if (fctx->tzname[0] == 0) {
+ fctx->current =
DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
+
TimestampTzGetDatum(fctx->current),
+
PointerGetDatum(&fctx->step)));
+ }
+ else
+ {
+ text *tzname_text = cstring_to_text(fctx->tzname);
+
+ fctx->current =
DatumGetTimestampTz(DirectFunctionCall3(timestamptz_pl_interval,
+
TimestampTzGetDatum(fctx->current),
+
PointerGetDatum(&fctx->step),
+
PointerGetDatum(tzname_text)));
+ }
/* 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 87aa571a33..9e3223cb2c 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' },
+{ 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_pl_interval' },
# boolean aggregates
{ oid => '2515', descr => 'aggregate transition function',
@@ -11884,5 +11902,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)
