Skip to content

Commit 75bd846

Browse files
tglsfdctglsfdc
committed
Add functions to do timestamptz arithmetic in a non-default timezone.
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
1 parent 0e681cf commit 75bd846

File tree

6 files changed

+291
-44
lines changed

6 files changed

+291
-44
lines changed

‎doc/src/sgml/func.sgml

+85-8
Original file line numberDiff line numberDiff line change
@@ -9266,6 +9266,28 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
92669266
</para></entry>
92679267
</row>
92689268

9269+
<row>
9270+
<entry role="func_table_entry"><para role="func_signature">
9271+
<indexterm>
9272+
<primary>date_add</primary>
9273+
</indexterm>
9274+
<function>date_add</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
9275+
<returnvalue>timestamp with time zone</returnvalue>
9276+
</para>
9277+
<para>
9278+
Add an <type>interval</type> to a <type>timestamp with time
9279+
zone</type>, computing times of day and daylight-savings adjustments
9280+
according to the time zone named by the third argument, or the
9281+
current <xref linkend="guc-timezone"/> setting if that is omitted.
9282+
The form with two arguments is equivalent to the <type>timestamp with
9283+
time zone</type> <literal>+</literal> <type>interval</type> operator.
9284+
</para>
9285+
<para>
9286+
<literal>date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
9287+
<returnvalue>2021-10-31 23:00:00+00</returnvalue>
9288+
</para></entry>
9289+
</row>
9290+
92699291
<row>
92709292
<entry role="func_table_entry"><para role="func_signature">
92719293
<function>date_bin</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> )
@@ -9313,6 +9335,28 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
93139335
</para></entry>
93149336
</row>
93159337

9338+
<row>
9339+
<entry role="func_table_entry"><para role="func_signature">
9340+
<indexterm>
9341+
<primary>date_subtract</primary>
9342+
</indexterm>
9343+
<function>date_subtract</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
9344+
<returnvalue>timestamp with time zone</returnvalue>
9345+
</para>
9346+
<para>
9347+
Subtract an <type>interval</type> from a <type>timestamp with time
9348+
zone</type>, computing times of day and daylight-savings adjustments
9349+
according to the time zone named by the third argument, or the
9350+
current <xref linkend="guc-timezone"/> setting if that is omitted.
9351+
The form with two arguments is equivalent to the <type>timestamp with
9352+
time zone</type> <literal>-</literal> <type>interval</type> operator.
9353+
</para>
9354+
<para>
9355+
<literal>date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
9356+
<returnvalue>2021-10-30 22:00:00+00</returnvalue>
9357+
</para></entry>
9358+
</row>
9359+
93169360
<row>
93179361
<entry role="func_table_entry"><para role="func_signature">
93189362
<indexterm>
@@ -9808,13 +9852,23 @@ SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
98089852

98099853
<para>
98109854
When adding an <type>interval</type> value to (or subtracting an
9811-
<type>interval</type> value from) a <type>timestamp with time zone</type>
9812-
value, the days component advances or decrements the date of the
9813-
<type>timestamp with time zone</type> by the indicated number of days,
9814-
keeping the time of day the same.
9815-
Across daylight saving time changes (when the session time zone is set to a
9816-
time zone that recognizes DST), this means <literal>interval '1 day'</literal>
9817-
does not necessarily equal <literal>interval '24 hours'</literal>.
9855+
<type>interval</type> value from) a <type>timestamp</type>
9856+
or <type>timestamp with time zone</type> value, the months, days, and
9857+
microseconds fields of the <type>interval</type> value are handled in turn.
9858+
First, a nonzero months field advances or decrements the date of the
9859+
timestamp by the indicated number of months, keeping the day of month the
9860+
same unless it would be past the end of the new month, in which case the
9861+
last day of that month is used. (For example, March 31 plus 1 month
9862+
becomes April 30, but March 31 plus 2 months becomes May 31.)
9863+
Then the days field advances or decrements the date of the timestamp by
9864+
the indicated number of days. In both these steps the local time of day
9865+
is kept the same. Finally, if there is a nonzero microseconds field, it
9866+
is added or subtracted literally.
9867+
When doing arithmetic on a <type>timestamp with time zone</type> value in
9868+
a time zone that recognizes DST, this means that adding or subtracting
9869+
(say) <literal>interval '1 day'</literal> does not necessarily have the
9870+
same result as adding or subtracting <literal>interval '24
9871+
hours'</literal>.
98189872
For example, with the session time zone set
98199873
to <literal>America/Denver</literal>:
98209874
<screen>
@@ -22017,13 +22071,17 @@ AND
2201722071
<returnvalue>setof timestamp</returnvalue>
2201822072
</para>
2201922073
<para role="func_signature">
22020-
<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> )
22074+
<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> )
2202122075
<returnvalue>setof timestamp with time zone</returnvalue>
2202222076
</para>
2202322077
<para>
2202422078
Generates a series of values from <parameter>start</parameter>
2202522079
to <parameter>stop</parameter>, with a step size
2202622080
of <parameter>step</parameter>.
22081+
In the timezone-aware form, times of day and daylight-savings
22082+
adjustments are computed according to the time zone named by
22083+
the <parameter>timezone</parameter> argument, or the current
22084+
<xref linkend="guc-timezone"/> setting if that is omitted.
2202722085
</para></entry>
2202822086
</row>
2202922087
</tbody>
@@ -22091,6 +22149,25 @@ SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
2209122149
2008-03-03 22:00:00
2209222150
2008-03-04 08:00:00
2209322151
(9 rows)
22152+
22153+
-- this example assumes that TimeZone is set to UTC; note the DST transition:
22154+
SELECT * FROM generate_series('2001-10-22 00:00 -04:00'::timestamptz,
22155+
'2001-11-01 00:00 -05:00'::timestamptz,
22156+
'1 day'::interval, 'America/New_York');
22157+
generate_series
22158+
------------------------
22159+
2001-10-22 04:00:00+00
22160+
2001-10-23 04:00:00+00
22161+
2001-10-24 04:00:00+00
22162+
2001-10-25 04:00:00+00
22163+
2001-10-26 04:00:00+00
22164+
2001-10-27 04:00:00+00
22165+
2001-10-28 04:00:00+00
22166+
2001-10-29 05:00:00+00
22167+
2001-10-30 05:00:00+00
22168+
2001-10-31 05:00:00+00
22169+
2001-11-01 05:00:00+00
22170+
(11 rows)
2209422171
</programlisting>
2209522172
</para>
2209622173

0 commit comments

Comments
 (0)