Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 75bd846

Browse filesBrowse 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
Copy full SHA for 75bd846

File tree

Expand file treeCollapse file tree

6 files changed

+291
-44
lines changed
Filter options
Expand file treeCollapse file tree

6 files changed

+291
-44
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+85-8Lines changed: 85 additions & 8 deletions
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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.