summary | shortlog | log | commit | commitdiff | tree
raw | patch | inline | side by side (parent: 0e681cf)
raw | patch | inline | side by side (parent: 0e681cf)
| author | Tom Lane <tgl@sss.pgh.pa.us> | |
| Sat, 18 Mar 2023 18:12:14 +0000 (14:12 -0400) | ||
| committer | Tom Lane <tgl@sss.pgh.pa.us> | |
| Sat, 18 Mar 2023 18:12:16 +0000 (14:12 -0400) |
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
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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 15314aa3ee5aec2de585cfe1327978aae051cfe6..a3a13b895f23bf61faaa55f995244e83421bb09f 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
</para></entry>
</row>
+ <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> )
</para></entry>
</row>
+ <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>
<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>
<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>
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>
index c266d0d02edb031c68aa05a7f36b653c1d5da2be..aaadc68ae6a9d96ff759a0887f99624c1c2b170c 100644 (file)
TimestampTz finish;
Interval step;
int step_sign;
+ pg_tz *attimezone;
} generate_series_timestamptz_fctx;
return tz;
}
+/*
+ * 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
}
-/* 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;
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")));
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, ×tamp) != 0)
ereport(ERROR,
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")));
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, ×tamp) != 0)
ereport(ERROR,
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)
/* 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);
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
text *zone = PG_GETARG_TEXT_PP(2);
TimestampTz result;
- char tzname[TZ_STRLEN_MAX + 1];
pg_tz *tzp;
/*
/*
* 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);
}
/* 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;
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};
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);
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));
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);
+}
index b2eed22d46c9c8c6cfd1048528ed0fd43f86e0d6..e94528a7c71e8c8a3017d7f415e034e0747dc5cd 100644 (file)
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202303151
+#define CATALOG_VERSION_NO 202303181
#endif
index fbc4aade4940d697e8ce19bcf3e52788aa6bb23a..5cf87aeb2c41c34533b3ef6f182e28cb008dd0af 100644 (file)
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' },
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',
index 91d7c1f5cc2aa0f6689d95f1c72440e19447e599..0dd2fe2c82dc62bf92f5e5843165915a7eb94f0c 100644 (file)
'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)
index ae9ee4b56a28e6530e7d3377f953209c74268795..69b36d04202e24b1423bd43f94f967c9aa82a114 100644 (file)
'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)