From 75bd846b68b09f33a57d9de96064ded4d392ca05 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Sat, 18 Mar 2023 14:12:14 -0400
Subject: 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
---
doc/src/sgml/func.sgml | 93 +++++++++++++++++++++++++++++++++++++++++++++-----
1 file changed, 85 insertions(+), 8 deletions(-)
(limited to 'doc')
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 15314aa3ee5..a3a13b895f2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9266,6 +9266,28 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
+
+
+
+ date_add
+
+ date_add ( timestamp with time zone, interval , text )
+ timestamp with time zone
+
+
+ Add an interval to a timestamp with time
+ zone, computing times of day and daylight-savings adjustments
+ according to the time zone named by the third argument, or the
+ current setting if that is omitted.
+ The form with two arguments is equivalent to the timestamp with
+ time zone + interval operator.
+
+
+ date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')
+ 2021-10-31 23:00:00+00
+
+
+
date_bin ( interval, timestamp, timestamp )
@@ -9313,6 +9335,28 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
+
+
+
+ date_subtract
+
+ date_subtract ( timestamp with time zone, interval , text )
+ timestamp with time zone
+
+
+ Subtract an interval from a timestamp with time
+ zone, computing times of day and daylight-savings adjustments
+ according to the time zone named by the third argument, or the
+ current setting if that is omitted.
+ The form with two arguments is equivalent to the timestamp with
+ time zone - interval operator.
+
+
+ date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')
+ 2021-10-30 22:00:00+00
+
+
+
@@ -9808,13 +9852,23 @@ SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
When adding an interval value to (or subtracting an
- interval value from) a timestamp with time zone
- value, the days component advances or decrements the date of the
- timestamp with time zone 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 interval '1 day'
- does not necessarily equal interval '24 hours'.
+ interval value from) a timestamp
+ or timestamp with time zone value, the months, days, and
+ microseconds fields of the interval 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 timestamp with time zone value in
+ a time zone that recognizes DST, this means that adding or subtracting
+ (say) interval '1 day' does not necessarily have the
+ same result as adding or subtracting interval '24
+ hours'.
For example, with the session time zone set
to America/Denver:
@@ -22017,13 +22071,17 @@ AND
setof timestamp
- generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval )
+ generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval , timezone text )
setof timestamp with time zone
Generates a series of values from start
to stop, with a step size
of step.
+ In the timezone-aware form, times of day and daylight-savings
+ adjustments are computed according to the time zone named by
+ the timezone argument, or the current
+ setting if that is omitted.
@@ -22091,6 +22149,25 @@ SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
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)
--
cgit v1.2.3