Tomas Vondra wrote on 18.02.2024 01:29:
Hi,
Please don't too-post on this list. The custom is to bottom-post or
reply inline, and it's much easier to follow such replies.
On 12/23/23 23:45, Przemysław Sztoch wrote:
date_bin has big problem with DST.
In example, if you put origin in winter zone, then generated bin will be
incorrect for summer input date.
date_trunc is resistant for this problem.
My version of date_trunc is additionally more flexible, you can select
more granular interval, 12h, 8h, 6h, 15min, 10 min etc...
I'm not very familiar with date_bin(), but is this issue inherent or
could we maybe fix date_bin() to handle DST better?
Apparently the functionality is identical to date_bin.
When I saw date_bin in the documentation, I thought it solved all my
problems.
Unfortunately, DST problems have many corner cases.
I tried to change date_bin several times, but unfortunately in some
cases it would start working differently than before.
In any case, the patch needs to add the new stuff to the SGML docs (to
doc/src/sgml/func.sgml), which now documents the date_trunc(text,...)
variant only.
Updated.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..95cdfab2d0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9472,6 +9472,23 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>date_trunc</primary>
+ </indexterm>
+ <function>date_trunc</function> ( <type>interval</type>,
<type>timestamp with time zone</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Truncate to specified precision in the specified time zone. Interval
has to be a divisor of a day, week or century.
+ </para>
+ <para>
+ <literal>date_trunc('30 minutes'::interval, timestamp '2001-02-16
20:38:40+00')</literal>
+ <returnvalue>2001-02-16 20:30:00+00</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<function>date_trunc</function> ( <type>text</type>, <type>timestamp
with time zone</type>, <type>text</type> )
@@ -9487,6 +9504,24 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>date_trunc</function> ( <type>interval</type>,
<type>timestamp with time zone</type>, <type>text</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Truncate to specified precision in the specified time zone. Interval
has to be a divisor of a day, week or century.
+ </para>
+ <para>
+ <literal>date_trunc('3 hour'::interval, timestamptz '2001-02-16
21:38:40+00', 'Europe/Warsaw')</literal>
+ <returnvalue>2001-02-16 20:00:00+00</returnvalue>
+ </para>
+ <para>
+ <literal>date_trunc('15 minutes'::interval, timestamptz '2001-02-16
21:38:40+00', 'Europe/Warsaw')</literal>
+ <returnvalue>2001-02-16 21:30:00+00</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<function>date_trunc</function> ( <type>text</type>,
<type>interval</type> )
diff --git a/src/backend/utils/adt/timestamp.c
b/src/backend/utils/adt/timestamp.c
index 7a016a6923..e376968c49 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4999,6 +4999,177 @@ 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)
+ 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_int_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;
+ 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);
+
+ tzp = lookup_timezone(zone);
+
+ result = timestamptz_trunc_int_internal(interval, timestamp, tzp);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
/* interval_trunc()
* Extract specified field from interval.
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9c120fc2b7..b45c2afa6c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2481,6 +2481,14 @@
{ oid => '1218', descr => 'truncate interval to specified units',
proname => 'date_trunc', prorettype => 'interval',
proargtypes => 'text interval', prosrc => 'interval_trunc' },
+{ 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' },
{ oid => '1219', descr => 'increment',
proname => 'int8inc', prorettype => 'int8', proargtypes => 'int8',
diff --git a/src/test/regress/expected/timestamptz.out
b/src/test/regress/expected/timestamptz.out
index bfb3825ff6..25d2e65808 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -695,7 +695,7 @@ SELECT d1 - timestamp with time zone '1997-01-02' AS diff
@ 1460 days 17 hours 32 mins 1 sec
(56 rows)
-SELECT date_trunc( 'week', timestamp with time zone '2004-02-29
15:44:17.71393' ) AS week_trunc;
+SELECT date_trunc('week', timestamp with time zone '2004-02-29
15:44:17.71393') AS week_trunc;
week_trunc
------------------------------
Mon Feb 23 00:00:00 2004 PST
@@ -719,11 +719,67 @@ SELECT date_trunc('day', timestamp with time zone
'2001-02-16 20:38:40+00', 'VET
Thu Feb 15 20:00:00 2001 PST
(1 row)
+SELECT date_trunc('7 day'::interval, timestamp with time zone '2004-02-29
15:44:17.71393') AS week_trunc;
+ week_trunc
+------------------------------
+ Mon Feb 23 00:00:00 2004 PST
+(1 row)
+
+SELECT date_trunc('3 month'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Australia/Sydney') as sydney_trunc; -- zone name
+ sydney_trunc
+------------------------------
+ Sun Dec 31 05:00:00 2000 PST
+(1 row)
+
+SELECT date_trunc('12 hour'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'GMT') as gmt_trunc; -- fixed-offset abbreviation
+ gmt_trunc
+------------------------------
+ Fri Feb 16 04:00:00 2001 PST
+(1 row)
+
+SELECT date_trunc('6 hour'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
+ vet_trunc
+------------------------------
+ Fri Feb 16 08:00:00 2001 PST
+(1 row)
+
+SELECT date_trunc('6 minutes'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
+ vet_trunc
+------------------------------
+ Fri Feb 16 12:36:00 2001 PST
+(1 row)
+
+SELECT date_trunc('10 second'::interval, timestamp with time zone '2004-02-29
15:44:17.71393') AS tensec_trunc;
+ tensec_trunc
+------------------------------
+ Sun Feb 29 15:44:10 2004 PST
+(1 row)
+
+SELECT date_trunc('500 msecond'::interval, timestamp with time zone
'2004-02-29 15:44:17.71393') AS halfsec_trunc;
+ halfsec_trunc
+--------------------------------
+ Sun Feb 29 15:44:17.5 2004 PST
+(1 row)
+
+-- errors
+SELECT date_trunc('1 month 7 day'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval1;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('1 month 01:00:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval2;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('1 day 00:30:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval3;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('7 month'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval4;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('3 day'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval5;
+ERROR: interval has to be a divisor of a day, week or century.
+SELECT date_trunc('00:23:00'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval6;
+ERROR: interval has to be a divisor of a day, week or century.
-- verify date_bin behaves the same as date_trunc for relevant intervals
SELECT
str,
interval,
- date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts,
timestamp with time zone '2001-01-01+11') AS equal
+ date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts,
timestamp with time zone '2001-01-01+11') AS equal_str,
+ date_trunc(interval::interval, ts, 'Australia/Sydney') =
date_bin(interval::interval, ts, timestamp with time zone '2001-01-01+11') AS
equal_interval
FROM (
VALUES
('day', '1 d'),
@@ -734,14 +790,14 @@ FROM (
('microsecond', '1 us')
) intervals (str, interval),
(VALUES (timestamptz '2020-02-29 15:44:17.71393+00')) ts (ts);
- str | interval | equal
--------------+----------+-------
- day | 1 d | t
- hour | 1 h | t
- minute | 1 m | t
- second | 1 s | t
- millisecond | 1 ms | t
- microsecond | 1 us | t
+ str | interval | equal_str | equal_interval
+-------------+----------+-----------+----------------
+ day | 1 d | t | t
+ hour | 1 h | t | t
+ minute | 1 m | t | t
+ second | 1 s | t | t
+ millisecond | 1 ms | t | t
+ microsecond | 1 us | t | t
(6 rows)
-- bin timestamps into arbitrary intervals
@@ -2539,6 +2595,75 @@ SELECT * FROM generate_series('2021-12-31
23:00:00+00'::timestamptz,
Thu Dec 31 23:00:00 2020 UTC
(13 rows)
+SET TimeZone to 'Europe/Warsaw';
+-- DST - 23 hours in day
+SELECT ts,
+ date_trunc('1 hour'::interval, ts, 'Europe/Warsaw') AS one_hour_bin,
+ date_trunc('2 hour'::interval, ts, 'Europe/Warsaw') AS two_hours_bin,
+ date_trunc('3 hour'::interval, ts, 'Europe/Warsaw') AS three_hours_bin
+ FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
+ '2022-03-27 07:00:00+00'::timestamptz,
+ '30 min'::interval,
+ 'Europe/Warsaw') AS ts;
+ ts | one_hour_bin |
two_hours_bin | three_hours_bin
+-------------------------------+-------------------------------+-------------------------------+-------------------------------
+ Sat Mar 26 22:00:00 2022 CET | Sat Mar 26 22:00:00 2022 CET | Sat Mar 26
22:00:00 2022 CET | Sat Mar 26 21:00:00 2022 CET
+ Sat Mar 26 22:30:00 2022 CET | Sat Mar 26 22:00:00 2022 CET | Sat Mar 26
22:00:00 2022 CET | Sat Mar 26 21:00:00 2022 CET
+ Sat Mar 26 23:00:00 2022 CET | Sat Mar 26 23:00:00 2022 CET | Sat Mar 26
22:00:00 2022 CET | Sat Mar 26 21:00:00 2022 CET
+ Sat Mar 26 23:30:00 2022 CET | Sat Mar 26 23:00:00 2022 CET | Sat Mar 26
22:00:00 2022 CET | Sat Mar 26 21:00:00 2022 CET
+ Sun Mar 27 00:00:00 2022 CET | Sun Mar 27 00:00:00 2022 CET | Sun Mar 27
00:00:00 2022 CET | Sun Mar 27 00:00:00 2022 CET
+ Sun Mar 27 00:30:00 2022 CET | Sun Mar 27 00:00:00 2022 CET | Sun Mar 27
00:00:00 2022 CET | Sun Mar 27 00:00:00 2022 CET
+ Sun Mar 27 01:00:00 2022 CET | Sun Mar 27 01:00:00 2022 CET | Sun Mar 27
00:00:00 2022 CET | Sun Mar 27 00:00:00 2022 CET
+ Sun Mar 27 01:30:00 2022 CET | Sun Mar 27 01:00:00 2022 CET | Sun Mar 27
00:00:00 2022 CET | Sun Mar 27 00:00:00 2022 CET
+ Sun Mar 27 03:00:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST | Sun Mar 27
03:00:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST
+ Sun Mar 27 03:30:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST | Sun Mar 27
03:00:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST
+ Sun Mar 27 04:00:00 2022 CEST | Sun Mar 27 04:00:00 2022 CEST | Sun Mar 27
04:00:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST
+ Sun Mar 27 04:30:00 2022 CEST | Sun Mar 27 04:00:00 2022 CEST | Sun Mar 27
04:00:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST
+ Sun Mar 27 05:00:00 2022 CEST | Sun Mar 27 05:00:00 2022 CEST | Sun Mar 27
04:00:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST
+ Sun Mar 27 05:30:00 2022 CEST | Sun Mar 27 05:00:00 2022 CEST | Sun Mar 27
04:00:00 2022 CEST | Sun Mar 27 03:00:00 2022 CEST
+ Sun Mar 27 06:00:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST | Sun Mar 27
06:00:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST
+ Sun Mar 27 06:30:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST | Sun Mar 27
06:00:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST
+ Sun Mar 27 07:00:00 2022 CEST | Sun Mar 27 07:00:00 2022 CEST | Sun Mar 27
06:00:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST
+ Sun Mar 27 07:30:00 2022 CEST | Sun Mar 27 07:00:00 2022 CEST | Sun Mar 27
06:00:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST
+ Sun Mar 27 08:00:00 2022 CEST | Sun Mar 27 08:00:00 2022 CEST | Sun Mar 27
08:00:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST
+ Sun Mar 27 08:30:00 2022 CEST | Sun Mar 27 08:00:00 2022 CEST | Sun Mar 27
08:00:00 2022 CEST | Sun Mar 27 06:00:00 2022 CEST
+ Sun Mar 27 09:00:00 2022 CEST | Sun Mar 27 09:00:00 2022 CEST | Sun Mar 27
08:00:00 2022 CEST | Sun Mar 27 09:00:00 2022 CEST
+(21 rows)
+
+-- DST - 25 hours in day
+SELECT ts,
+ date_trunc('1 hour'::interval, ts, 'Europe/Warsaw') AS one_hour_bin,
+ date_trunc('2 hour'::interval, ts, 'Europe/Warsaw') AS two_hours_bin,
+ date_trunc('3 hour'::interval, ts, 'Europe/Warsaw') AS three_hours_bin
+ FROM generate_series('2022-10-29 21:00:00+00'::timestamptz,
+ '2022-10-30 07:00:00+00'::timestamptz,
+ '30 min'::interval,
+ 'Europe/Warsaw') AS ts;
+ ts | one_hour_bin |
two_hours_bin | three_hours_bin
+-------------------------------+-------------------------------+-------------------------------+-------------------------------
+ Sat Oct 29 23:00:00 2022 CEST | Sat Oct 29 23:00:00 2022 CEST | Sat Oct 29
22:00:00 2022 CEST | Sat Oct 29 21:00:00 2022 CEST
+ Sat Oct 29 23:30:00 2022 CEST | Sat Oct 29 23:00:00 2022 CEST | Sat Oct 29
22:00:00 2022 CEST | Sat Oct 29 21:00:00 2022 CEST
+ Sun Oct 30 00:00:00 2022 CEST | Sun Oct 30 00:00:00 2022 CEST | Sun Oct 30
00:00:00 2022 CEST | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 00:30:00 2022 CEST | Sun Oct 30 00:00:00 2022 CEST | Sun Oct 30
00:00:00 2022 CEST | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 01:00:00 2022 CEST | Sun Oct 30 01:00:00 2022 CEST | Sun Oct 30
00:00:00 2022 CEST | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 01:30:00 2022 CEST | Sun Oct 30 01:00:00 2022 CEST | Sun Oct 30
00:00:00 2022 CEST | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 02:00:00 2022 CEST | Sun Oct 30 02:00:00 2022 CEST | Sun Oct 30
02:00:00 2022 CET | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 02:30:00 2022 CEST | Sun Oct 30 02:00:00 2022 CEST | Sun Oct 30
02:00:00 2022 CET | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 02:00:00 2022 CET | Sun Oct 30 02:00:00 2022 CET | Sun Oct 30
02:00:00 2022 CET | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 02:30:00 2022 CET | Sun Oct 30 02:00:00 2022 CET | Sun Oct 30
02:00:00 2022 CET | Sun Oct 30 00:00:00 2022 CEST
+ Sun Oct 30 03:00:00 2022 CET | Sun Oct 30 03:00:00 2022 CET | Sun Oct 30
02:00:00 2022 CET | Sun Oct 30 03:00:00 2022 CET
+ Sun Oct 30 03:30:00 2022 CET | Sun Oct 30 03:00:00 2022 CET | Sun Oct 30
02:00:00 2022 CET | Sun Oct 30 03:00:00 2022 CET
+ Sun Oct 30 04:00:00 2022 CET | Sun Oct 30 04:00:00 2022 CET | Sun Oct 30
04:00:00 2022 CET | Sun Oct 30 03:00:00 2022 CET
+ Sun Oct 30 04:30:00 2022 CET | Sun Oct 30 04:00:00 2022 CET | Sun Oct 30
04:00:00 2022 CET | Sun Oct 30 03:00:00 2022 CET
+ Sun Oct 30 05:00:00 2022 CET | Sun Oct 30 05:00:00 2022 CET | Sun Oct 30
04:00:00 2022 CET | Sun Oct 30 03:00:00 2022 CET
+ Sun Oct 30 05:30:00 2022 CET | Sun Oct 30 05:00:00 2022 CET | Sun Oct 30
04:00:00 2022 CET | Sun Oct 30 03:00:00 2022 CET
+ Sun Oct 30 06:00:00 2022 CET | Sun Oct 30 06:00:00 2022 CET | Sun Oct 30
06:00:00 2022 CET | Sun Oct 30 06:00:00 2022 CET
+ Sun Oct 30 06:30:00 2022 CET | Sun Oct 30 06:00:00 2022 CET | Sun Oct 30
06:00:00 2022 CET | Sun Oct 30 06:00:00 2022 CET
+ Sun Oct 30 07:00:00 2022 CET | Sun Oct 30 07:00:00 2022 CET | Sun Oct 30
06:00:00 2022 CET | Sun Oct 30 06:00:00 2022 CET
+ Sun Oct 30 07:30:00 2022 CET | Sun Oct 30 07:00:00 2022 CET | Sun Oct 30
06:00:00 2022 CET | Sun Oct 30 06:00:00 2022 CET
+ Sun Oct 30 08:00:00 2022 CET | Sun Oct 30 08:00:00 2022 CET | Sun Oct 30
08:00:00 2022 CET | Sun Oct 30 06:00:00 2022 CET
+(21 rows)
+
RESET TimeZone;
--
-- Test behavior with a dynamic (time-varying) timezone abbreviation.
diff --git a/src/test/regress/sql/timestamptz.sql
b/src/test/regress/sql/timestamptz.sql
index ccfd90d646..701cd79094 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -199,17 +199,33 @@ SELECT d1 FROM TIMESTAMPTZ_TBL
SELECT d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-SELECT date_trunc( 'week', timestamp with time zone '2004-02-29
15:44:17.71393' ) AS week_trunc;
-
+SELECT date_trunc('week', timestamp with time zone '2004-02-29
15:44:17.71393') AS week_trunc;
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00',
'Australia/Sydney') as sydney_trunc; -- zone name
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00',
'GMT') as gmt_trunc; -- fixed-offset abbreviation
SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00',
'VET') as vet_trunc; -- variable-offset abbreviation
+SELECT date_trunc('7 day'::interval, timestamp with time zone '2004-02-29
15:44:17.71393') AS week_trunc;
+SELECT date_trunc('3 month'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Australia/Sydney') as sydney_trunc; -- zone name
+SELECT date_trunc('12 hour'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'GMT') as gmt_trunc; -- fixed-offset abbreviation
+SELECT date_trunc('6 hour'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
+SELECT date_trunc('6 minutes'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'VET') as vet_trunc; -- variable-offset abbreviation
+SELECT date_trunc('10 second'::interval, timestamp with time zone '2004-02-29
15:44:17.71393') AS tensec_trunc;
+SELECT date_trunc('500 msecond'::interval, timestamp with time zone
'2004-02-29 15:44:17.71393') AS halfsec_trunc;
+
+-- errors
+SELECT date_trunc('1 month 7 day'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval1;
+SELECT date_trunc('1 month 01:00:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval2;
+SELECT date_trunc('1 day 00:30:00'::interval, timestamp with time zone
'2001-02-16 20:38:40+00', 'Europe/Warsaw') AS bad_interval3;
+SELECT date_trunc('7 month'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval4;
+SELECT date_trunc('3 day'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval5;
+SELECT date_trunc('00:23:00'::interval, timestamp with time zone '2001-02-16
20:38:40+00', 'Europe/Warsaw') AS bad_interval6;
+
-- verify date_bin behaves the same as date_trunc for relevant intervals
SELECT
str,
interval,
- date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts,
timestamp with time zone '2001-01-01+11') AS equal
+ date_trunc(str, ts, 'Australia/Sydney') = date_bin(interval::interval, ts,
timestamp with time zone '2001-01-01+11') AS equal_str,
+ date_trunc(interval::interval, ts, 'Australia/Sydney') =
date_bin(interval::interval, ts, timestamp with time zone '2001-01-01+11') AS
equal_interval
FROM (
VALUES
('day', '1 d'),
@@ -486,6 +502,26 @@ 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');
+SET TimeZone to 'Europe/Warsaw';
+-- DST - 23 hours in day
+SELECT ts,
+ date_trunc('1 hour'::interval, ts, 'Europe/Warsaw') AS one_hour_bin,
+ date_trunc('2 hour'::interval, ts, 'Europe/Warsaw') AS two_hours_bin,
+ date_trunc('3 hour'::interval, ts, 'Europe/Warsaw') AS three_hours_bin
+ FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
+ '2022-03-27 07:00:00+00'::timestamptz,
+ '30 min'::interval,
+ 'Europe/Warsaw') AS ts;
+-- DST - 25 hours in day
+SELECT ts,
+ date_trunc('1 hour'::interval, ts, 'Europe/Warsaw') AS one_hour_bin,
+ date_trunc('2 hour'::interval, ts, 'Europe/Warsaw') AS two_hours_bin,
+ date_trunc('3 hour'::interval, ts, 'Europe/Warsaw') AS three_hours_bin
+ FROM generate_series('2022-10-29 21:00:00+00'::timestamptz,
+ '2022-10-30 07:00:00+00'::timestamptz,
+ '30 min'::interval,
+ 'Europe/Warsaw') AS ts;
+
RESET TimeZone;
--
