Przemysław Sztoch wrote on 14.06.2022 21:46:
Tom Lane wrote on 14.06.2022 15:43:
=?UTF-8?Q?Przemys=c5=82aw_Sztoch?=<przemys...@sztoch.pl> writes:
Please let me know what is the convention (procedure) of adding new
functions to pg_proc. Specifically how oid is allocated.
See
https://www.postgresql.org/docs/devel/system-catalog-initial-data.html#SYSTEM-CATALOG-OID-ASSIGNMENT
(you should probably read that whole chapter for context).
Thx.
There is another patch.
It works, but one thing is wrongly done because I lack knowledge.
Where I'm using DirectFunctionCall3 I need to pass the timezone name,
but I'm using cstring_to_text and I'm pretty sure there's a memory
leak here. But I need help to fix this.
I don't know how best to store the timezone in the generate_series
context. Please, help.
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.
Additionally, I added a new variant of the date_trunc function that
takes intervals as an argument.
It enables functionality similar to date_bin, but supports monthly,
quarterly, annual, etc. periods.
In addition, it is resistant to the problems of different time zones and
daylight saving time (DST).
--
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..98ca7f8d53 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;
@@ -3003,83 +3004,124 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
{
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
Interval *span = PG_GETARG_INTERVAL_P(1);
- TimestampTz result;
+ pg_tz *attimezone = NULL;
int tz;
if (TIMESTAMP_NOT_FINITE(timestamp))
- result = timestamp;
- else
+ PG_RETURN_TIMESTAMP(timestamp);
+
+ if (PG_NARGS() > 2)
{
- if (span->month != 0)
+ text *zone = PG_GETARG_TEXT_PP(2);
+ char tzname[TZ_STRLEN_MAX + 1];
+ char *lowzone;
+ int type,
+ val;
+ pg_tz *tzp;
+ /*
+ * Look up the requested timezone (see notes in
timestamptz_zone()).
+ */
+ text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+ /* DecodeTimezoneAbbrev requires lowercase input */
+ lowzone = downcase_truncate_identifier(tzname,
+
strlen(tzname),
+
false);
+
+ type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+ if (type == TZ || type == DTZ)
{
- struct pg_tm tt,
- *tm = &tt;
- fsec_t fsec;
-
- if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL)
!= 0)
- ereport(ERROR,
-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("timestamp out of
range")));
-
- 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")));
+ /* fixed-offset abbreviation, get a pg_tz descriptor
for that */
+ tzp = pg_tzset_offset(-val);
}
-
- if (span->day != 0)
+ else if (type == DYNTZ)
{
- struct pg_tm tt,
- *tm = &tt;
- fsec_t fsec;
- int julian;
-
- if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL)
!= 0)
- 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")));
+ /* 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)));
+ }
+ attimezone = tzp;
+ }
- timestamp += span->time;
+ /* 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 (!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
@@ -4324,6 +4366,247 @@ timestamptz_trunc_zone(PG_FUNCTION_ARGS)
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;
+ char tzname[TZ_STRLEN_MAX + 1];
+ char *lowzone;
+ int type,
+ val;
+ 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);
+
+ /*
+ * 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)));
+ }
+
+ result = timestamptz_trunc_int_internal(interval, timestamp, tzp);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
/* interval_trunc()
* Extract specified field from interval.
*/
@@ -5888,6 +6171,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 +6198,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..52f42f0044 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2429,7 +2429,7 @@
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',
@@ -11885,4 +11885,23 @@
prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
prosrc => 'brin_minmax_multi_summary_send' },
+# timestamptz plus interval with timezone patch
+{ 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' },
+{ 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 => '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' },
]
