date_trunc function in interval version
Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15
minute', '10 second' etc.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Attachments:
date_trunc_interval_version-v1.patchtext/plain; charset=UTF-8; name=date_trunc_interval_version-v1.patchDownload
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index eebc59172b..90fd253d0c 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4940,6 +4940,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_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 b8b26c263d..01393a4d4f 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 a084357480..c799ba224b 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
@@ -2525,6 +2581,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 a2dcd5f5d8..f7941e48b4 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'),
@@ -478,6 +494,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;
--
Hi
pá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl>
napsal:
Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15
minute', '10 second' etc.
should not be named interval_trunc instead? In this case the good name can
be hard to choose, but with the name date_trunc it can be hard to find it.
Regards
Pavel
Show quoted text
--
Przemysław Sztoch | Mobile +48 509 99 00 66
In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new
version.
New version provides only more granularity.
Pavel Stehule wrote on 12/22/2023 8:43 PM:
Hi
pá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch
<przemyslaw@sztoch.pl <mailto:przemyslaw@sztoch.pl>> napsal:Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour',
'15 minute', '10 second' etc.should not be named interval_trunc instead? In this case the good name
can be hard to choose, but with the name date_trunc it can be hard to
find it.Regards
Pavel
--
Przemysław Sztoch | Mobile +48 509 99 00 66
--
Przemysław Sztoch | Mobile +48 509 99 00 66
On Sat, Dec 23, 2023 at 5:26 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:
In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new version.
New version provides only more granularity.
I haven't looked at the patch, but your description sounds awfully
close to date_bin(), which already takes an arbitrary interval.
Hi
pá 22. 12. 2023 v 23:25 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl>
napsal:
In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new
version.
New version provides only more granularity.
ok, I miss it.
Regards
Pavel
Show quoted text
Pavel Stehule wrote on 12/22/2023 8:43 PM:
Hi
pá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl>
napsal:Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15
minute', '10 second' etc.should not be named interval_trunc instead? In this case the good name can
be hard to choose, but with the name date_trunc it can be hard to find it.Regards
Pavel
--
Przemysław Sztoch | Mobile +48 509 99 00 66--
Przemysław Sztoch | Mobile +48 509 99 00 66
so 23. 12. 2023 v 13:33 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
Hi
pá 22. 12. 2023 v 23:25 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl>
napsal:In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new
version.
New version provides only more granularity.ok, I miss it.
I was confused - I am sorry, I imagined something different. Then the name
is correct.
Regards
Pavel
Show quoted text
Pavel Stehule wrote on 12/22/2023 8:43 PM:
Hi
pá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl>
napsal:Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15
minute', '10 second' etc.should not be named interval_trunc instead? In this case the good name can
be hard to choose, but with the name date_trunc it can be hard to find it.Regards
Pavel
--
Przemysław Sztoch | Mobile +48 509 99 00 66--
Przemysław Sztoch | Mobile +48 509 99 00 66
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...
John Naylor wrote on 23.12.2023 01:32:
On Sat, Dec 23, 2023 at 5:26 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:
In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new version.
New version provides only more granularity.I haven't looked at the patch, but your description sounds awfully
close to date_bin(), which already takes an arbitrary interval.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
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?
In particular, isn't part of the problem that date_bin() is defined only
for timestamp and not for timestamptz? Also, date_trunc() allows to
specify a timezone, but date_bin() does not.
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.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 18 Feb 2024, at 05:29, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
I'm not very familiar with date_bin(), but is this issue inherent or
could we maybe fix date_bin() to handle DST better?In particular, isn't part of the problem that date_bin() is defined only
for timestamp and not for timestamptz? Also, date_trunc() allows to
specify a timezone, but date_bin() does not.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.
Hi Przemysław,
Please address above notes.
I’ve flipped CF entry [0]https://commitfest.postgresql.org/47/4761/ to “Waiting on author”, feel free to switch it back.
Thank you!
Best regards, Andrey Borodin.
[0]: https://commitfest.postgresql.org/47/4761/
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
Attachments:
date_trunc_interval_version-v2.patchtext/plain; charset=UTF-8; name=date_trunc_interval_version-v2.patchDownload
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;
--
On Mon, Mar 4, 2024 at 5:03 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:
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.
So, first of all, thanks for taking an interest and sending a patch.
In order for the patch to have a chance of being accepted, we would
need to have a clear understanding of exactly how this patch is
different from the existing date_bin(). If we knew that, we could
decide either that (a) date_bin does the right thing and your patch
does the wrong thing and therefore we should reject your patch, or we
could decide that (b) date_bin does the wrong thing and therefore we
should fix it, or we could decide that (c) both date_bin and what this
patch does are correct, in the sense of being sensible things to do,
and there is a reason to have both. But if we don't really understand
how they are different, which seems to be the case right now, then we
can't make any decisions. And what that means in practice is that
nobody is going to be willing to commit anything, and we're just going
to go around in circles.
Typically, this kind of research is the responsibility of the patch
author: you're the one who wants something changed, so that means you
need to provide convincing evidence that it should be. If someone else
volunteers to do it, that's also cool, but it absolutely has to be
done in order for there to be a chance of progress here. No committer
is going to say "well, we already have date_bin, but Przemysław says
his date_trunc is different somehow, so let's have both without
understanding how exactly they're different." That's just not a
realistic scenario. Just to name one problem, how would we document
each of them? Users would expect the documentation to explain how two
closely-related functions differ, but we will be unable to explain
that if we don't know the answer ourselves.
If you can't figure out exactly what the differences are by code
inspection, then maybe one thing you could do to help unblock things
here is provide some very clear examples of when they deliver the same
results and when they deliver different results. Although there are no
guarantees, that might lead somebody else to jump in and suggest an
explanation, or further avenues of analysis, or some other helpful
comment.
Personally, what I suspect is that there's already a way to do what
you want using date_bin(), maybe in conjunction with some casting or
some calls to other functions that we already have. But it's hard to
be sure because we just don't have the details. "DST problems have
many corner cases" and "in some cases [date_bin] would start working
differently than before" may be true statements as far as they go, but
they're not very specific complaints. If you can describe *exactly*
how date_bin fails to meet your expectations, there is a much better
chance that something useful will happen here.
--
Robert Haas
EDB: http://www.enterprisedb.com
Robert Haas wrote on 5/15/2024 9:29 PM:
On Mon, Mar 4, 2024 at 5:03 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:
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.So, first of all, thanks for taking an interest and sending a patch.
In order for the patch to have a chance of being accepted, we would
need to have a clear understanding of exactly how this patch is
different from the existing date_bin(). If we knew that, we could
decide either that (a) date_bin does the right thing and your patch
does the wrong thing and therefore we should reject your patch, or we
could decide that (b) date_bin does the wrong thing and therefore we
should fix it, or we could decide that (c) both date_bin and what this
patch does are correct, in the sense of being sensible things to do,
and there is a reason to have both. But if we don't really understand
how they are different, which seems to be the case right now, then we
can't make any decisions. And what that means in practice is that
nobody is going to be willing to commit anything, and we're just going
to go around in circles.Typically, this kind of research is the responsibility of the patch
author: you're the one who wants something changed, so that means you
need to provide convincing evidence that it should be. If someone else
volunteers to do it, that's also cool, but it absolutely has to be
done in order for there to be a chance of progress here. No committer
is going to say "well, we already have date_bin, but Przemysław says
his date_trunc is different somehow, so let's have both without
understanding how exactly they're different." That's just not a
realistic scenario. Just to name one problem, how would we document
each of them? Users would expect the documentation to explain how two
closely-related functions differ, but we will be unable to explain
that if we don't know the answer ourselves.If you can't figure out exactly what the differences are by code
inspection, then maybe one thing you could do to help unblock things
here is provide some very clear examples of when they deliver the same
results and when they deliver different results. Although there are no
guarantees, that might lead somebody else to jump in and suggest an
explanation, or further avenues of analysis, or some other helpful
comment.Personally, what I suspect is that there's already a way to do what
you want using date_bin(), maybe in conjunction with some casting or
some calls to other functions that we already have. But it's hard to
be sure because we just don't have the details. "DST problems have
many corner cases" and "in some cases [date_bin] would start working
differently than before" may be true statements as far as they go, but
they're not very specific complaints. If you can describe *exactly*
how date_bin fails to meet your expectations, there is a much better
chance that something useful will happen here.
I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to
original function date_trunc ( text, timestamp ...) .
My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the
same with minutes.
We can round to 30 minutes, 20minutes, 15 minutes, etc.
Using date_bin has a similar effect, but requires specifying the origin.
According to this origin,
subsequent buckets are then calculated. The need to provide this origin
is sometimes a very big problem.
Especially since you cannot use one origin when changing from summer to
winter time.
If we use one origin for example begin of year: 2024-01-01 00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01
00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works ok,
because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01
00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has problem,
because we are in May after DST
If anyone has an idea how to make date_bin work like date_trunc, please
provide an example.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
On Sun, May 19, 2024 at 2:20 AM Przemysław Sztoch <przemyslaw@sztoch.pl>
wrote:
Robert Haas wrote on 5/15/2024 9:29 PM:
On Mon, Mar 4, 2024 at 5:03 AM Przemysław Sztoch <przemyslaw@sztoch.pl> <przemyslaw@sztoch.pl> wrote:
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.So, first of all, thanks for taking an interest and sending a patch.
In order for the patch to have a chance of being accepted, we would
need to have a clear understanding of exactly how this patch is
different from the existing date_bin(). If we knew that, we could
decide either that (a) date_bin does the right thing and your patch
does the wrong thing and therefore we should reject your patch, or we
could decide that (b) date_bin does the wrong thing and therefore we
should fix it, or we could decide that (c) both date_bin and what this
patch does are correct, in the sense of being sensible things to do,
and there is a reason to have both. But if we don't really understand
how they are different, which seems to be the case right now, then we
can't make any decisions. And what that means in practice is that
nobody is going to be willing to commit anything, and we're just going
to go around in circles.Typically, this kind of research is the responsibility of the patch
author: you're the one who wants something changed, so that means you
need to provide convincing evidence that it should be. If someone else
volunteers to do it, that's also cool, but it absolutely has to be
done in order for there to be a chance of progress here. No committer
is going to say "well, we already have date_bin, but Przemysław says
his date_trunc is different somehow, so let's have both without
understanding how exactly they're different." That's just not a
realistic scenario. Just to name one problem, how would we document
each of them? Users would expect the documentation to explain how two
closely-related functions differ, but we will be unable to explain
that if we don't know the answer ourselves.If you can't figure out exactly what the differences are by code
inspection, then maybe one thing you could do to help unblock things
here is provide some very clear examples of when they deliver the same
results and when they deliver different results. Although there are no
guarantees, that might lead somebody else to jump in and suggest an
explanation, or further avenues of analysis, or some other helpful
comment.Personally, what I suspect is that there's already a way to do what
you want using date_bin(), maybe in conjunction with some casting or
some calls to other functions that we already have. But it's hard to
be sure because we just don't have the details. "DST problems have
many corner cases" and "in some cases [date_bin] would start working
differently than before" may be true statements as far as they go, but
they're not very specific complaints. If you can describe *exactly*
how date_bin fails to meet your expectations, there is a much better
chance that something useful will happen here.I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to original
function date_trunc ( text, timestamp ...) .My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the
same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc.Using date_bin has a similar effect, but requires specifying the origin.
According to this origin,
subsequent buckets are then calculated. The need to provide this origin is
sometimes a very big problem.
Especially since you cannot use one origin when changing from summer to
winter time.If we use one origin for example begin of year: 2024-01-01 00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01 00:00:00'),
date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works ok,
because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01 00:00:00'),
date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has
problem, because we are in May after DSTIf anyone has an idea how to make date_bin work like date_trunc, please
provide an example.
Here is an example which will make date_bin() to behave like date_trunc():
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '0001-01-01'::timestamp),
date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
date_bin | date_trunc
---------------------+------------------------
2024-05-05 00:00:00 | 2024-05-05 00:00:00+02
(1 row)
In general, to make date_bin work similarly to date_trunc in PostgreSQL,
you need to set the interval length appropriately and use an origin
timestamp that aligns with the start of the interval you want to bin.
Here's how you can use date_bin to mimic the behavior of date_trunc:
Truncate to the Start of the Year:
# SELECT date_bin('1 year', timestamp_column, '0001-01-01'::timestamp) FROM
your_table;
Truncate to the Start of the Month:
# SELECT date_bin('1 month', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
Truncate to the Start of the Day:
# SELECT date_bin('1 day', timestamp_column, '0001-01-01'::timestamp) FROM
your_table;
Truncate to the Start of the Hour:
# SELECT date_bin('1 hour', timestamp_column, '0001-01-01'::timestamp) FROM
your_table;
Truncate to the Start of the Minute:
# SELECT date_bin('1 minute', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
--
Show quoted text
Przemysław Sztoch | Mobile +48 509 99 00 66
Yasir wrote on 19.05.2024 00:03:
I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to
original function date_trunc ( text, timestamp ...) .My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's
the same with minutes.
We can round to 30 minutes, 20minutes, 15 minutes, etc.Using date_bin has a similar effect, but requires specifying the
origin. According to this origin,
subsequent buckets are then calculated. The need to provide this
origin is sometimes a very big problem.
Especially since you cannot use one origin when changing from
summer to winter time.If we use one origin for example begin of year: 2024-01-01
00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01
00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works
ok, because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01
00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has
problem, because we are in May after DSTIf anyone has an idea how to make date_bin work like date_trunc,
please provide an example.Here is an example which will make date_bin() to behave like
date_trunc():
# SELECT date_bin('1 day', '2024-05-05 11:22:33',
'0001-01-01'::timestamp), date_trunc('day', '2024-05-05
11:22:33'::timestamptz);
date_bin | date_trunc
---------------------+------------------------
2024-05-05 00:00:00 | 2024-05-05 00:00:00+02
(1 row)In general, to make date_bin work similarly to date_trunc in
PostgreSQL, you need to set the interval length appropriately and use
an origin timestamp that aligns with the start of the interval you
want to bin.Here's how you can use date_bin to mimic the behavior of date_trunc:
Truncate to the Start of the Year:
# SELECT date_bin('1 year', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
Truncate to the Start of the Month:
# SELECT date_bin('1 month', timestamp_column,
'0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Day:
# SELECT date_bin('1 day', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
Truncate to the Start of the Hour:
# SELECT date_bin('1 hour', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
Truncate to the Start of the Minute:
# SELECT date_bin('1 minute', timestamp_column,
'0001-01-01'::timestamp) FROM your_table;--
Przemysław Sztoch | Mobile +48 509 99 00 66
Please, use it with timestamptz for '2 hours' or '3 hours' interval.
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27
01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27
01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27
01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27
01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27
08:36:00+02 | 2022-03-27 06:36:00+02
(21 rows)
We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin
| three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27
09:00:00+02 | 2022-03-27 07:00:00+02
(21 rows)
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Yasir wrote on 19.05.2024 00:03:
On Sun, May 19, 2024 at 2:20 AM Przemysław Sztoch
<przemyslaw@sztoch.pl <mailto:przemyslaw@sztoch.pl>> wrote:I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to
original function date_trunc ( text, timestamp ...) .My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's
the same with minutes.
We can round to 30 minutes, 20minutes, 15 minutes, etc.Using date_bin has a similar effect, but requires specifying the
origin. According to this origin,
subsequent buckets are then calculated. The need to provide this
origin is sometimes a very big problem.
Especially since you cannot use one origin when changing from
summer to winter time.If we use one origin for example begin of year: 2024-01-01
00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01
00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works
ok, because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01
00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has
problem, because we are in May after DSTIf anyone has an idea how to make date_bin work like date_trunc,
please provide an example.Here is an example which will make date_bin() to behave like
date_trunc():
# SELECT date_bin('1 day', '2024-05-05 11:22:33',
'0001-01-01'::timestamp), date_trunc('day', '2024-05-05
11:22:33'::timestamptz);
date_bin | date_trunc
---------------------+------------------------
2024-05-05 00:00:00 | 2024-05-05 00:00:00+02
(1 row)In general, to make date_bin work similarly to date_trunc in
PostgreSQL, you need to set the interval length appropriately and use
an origin timestamp that aligns with the start of the interval you
want to bin.Here's how you can use date_bin to mimic the behavior of date_trunc:
Truncate to the Start of the Year:
# SELECT date_bin('1 year', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
Truncate to the Start of the Month:
# SELECT date_bin('1 month', timestamp_column,
'0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Day:
# SELECT date_bin('1 day', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
Truncate to the Start of the Hour:
# SELECT date_bin('1 hour', timestamp_column, '0001-01-01'::timestamp)
FROM your_table;
Truncate to the Start of the Minute:
# SELECT date_bin('1 minute', timestamp_column,
'0001-01-01'::timestamp) FROM your_table;
Please, use it with timestamptz for '2 hours' or '3 hours' interval.
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01
| 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01
| 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 08:36:00+02
| 2022-03-27 06:36:00+02
(21 rows)
We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin
| three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02
| 2022-03-27 07:00:00+02
(21 rows)
--
Przemysław Sztoch | Mobile +48 509 99 00 66
On Mon, May 20, 2024 at 11:58 AM Przemysław Sztoch <przemyslaw@sztoch.pl>
wrote:
Yasir wrote on 19.05.2024 00:03:
I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to original
function date_trunc ( text, timestamp ...) .My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the
same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc....
Please, use it with timestamptz for '2 hours' or '3 hours' interval.
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01
| 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01
| 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 08:36:00+02
| 2022-03-27 06:36:00+02
(21 rows)We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin
| three_hours_bin------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02
| 2022-03-27 07:00:00+02
(21 rows)--
Przemysław Sztoch | Mobile +48 509 99 00 66
Forgive me, I saw this in the CF and wanted to review it because this
looked useful.
I cannot tell from your output what the differences would be vs. your
proposed date_trunc().
I was actually expecting columns: RowNum(), ts, date_bin, new date_trunc()
Where you explained the differences (maybe using the row number).
It appears your issue is the 36 Minutes. And it does beg the question
"Where is that coming from".
Finally, I assume that: even if you could fix it by using "AT UTC" to do
the grouping, and then change it to 'Europe/Warsaw'...
That 36 minutes probably creeps back in.
It "Feels" like the wrong answer, considering the inputs.
Finally... NOBODY Chimed in after you provided this evidence. Was this
accepted as proof, or was MORE expected?
Kirk
On 8/20/2025 9:52 PM, Kirk Wolak wrote:
On Mon, May 20, 2024 at 11:58 AM Przemysław Sztoch
<przemyslaw@sztoch.pl> wrote:Yasir wrote on 19.05.2024 00:03:
I would also like to thank Robert for presenting the matter
in detail.My function date_trunc ( interval, timestamp, ...) is similar
to original function date_trunc ( text, timestamp ...) .My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h.
It's the same with minutes.
We can round to 30 minutes, 20minutes, 15 minutes, etc....
Please, use it with timestamptz for '2 hours' or '3 hours' interval.
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26
21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26
23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27
01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27
01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27
01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27
01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27
04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27
06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27
08:36:00+02 | 2022-03-27 06:36:00+02
(21 rows)We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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;^C
postgres=# \e
ts | one_hour_bin |
two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26
22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27
00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27
03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27
05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27
07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27
09:00:00+02 | 2022-03-27 07:00:00+02
(21 rows)--
Przemysław Sztoch | Mobile +48 509 99 00 66Forgive me, I saw this in the CF and wanted to review it because this
looked useful.
I cannot tell from your output what the differences would be vs. your
proposed date_trunc().
I was actually expecting columns: RowNum(), ts, date_bin, new
date_trunc()
Where you explained the differences (maybe using the row number).It appears your issue is the 36 Minutes. And it does beg the question
"Where is that coming from".Finally, I assume that: even if you could fix it by using "AT UTC" to
do the grouping, and then change it to 'Europe/Warsaw'...
That 36 minutes probably creeps back in.
It "Feels" like the wrong answer, considering the inputs.Finally... NOBODY Chimed in after you provided this evidence. Was
this accepted as proof, or was MORE expected?Kirk
1. date_bin works good if you do not have changed time zone (for example
from summer to winter time).
date_bin simply adds constant interval - if you want to round your time
to 3 hours, 6 hours or 12 hours then you have problem if you want to
pass time zone changing point, because some times you want to add
interval without lack hour or add interval with extra hour.
original date_trunc works very good with DST problem, but has limited
granularity, you can't round timestamp to 5 min, 10 min, 30 min, 3
hours, 6 hours etc.
My data_trunc version with interval as period argument is able to
correctly overcome the time points at which the change from summer to
winter time and vice versa occurred for custom periods.
Additionally, it does not require specifying the origin time, which can
sometimes be very difficult to determine. You can't simple use
'0001-01-01 00:00:00' because it is problematic for some timezones.
Then it must be defined differently for each time zone. This complicates
queries when you work with different zones.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
On Wed, Aug 20, 2025 at 5:13 PM Przemysław Sztoch <przemyslaw@sztoch.pl>
wrote:
On 8/20/2025 9:52 PM, Kirk Wolak wrote:
On Mon, May 20, 2024 at 11:58 AM Przemysław Sztoch <przemyslaw@sztoch.pl>
wrote:Yasir wrote on 19.05.2024 00:03:
I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to
original function date_trunc ( text, timestamp ...) .My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the
same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc....
Please, use it with timestamptz for '2 hours' or '3 hours' interval.
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01
| 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01
| 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01
| 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01
| 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02
| 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02
| 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 08:36:00+02
| 2022-03-27 06:36:00+02
(21 rows)We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS
one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS
two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') 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;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin
| three_hours_bin------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01
| 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01
| 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02
| 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02
| 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02
| 2022-03-27 07:00:00+02
(21 rows)--
Przemysław Sztoch | Mobile +48 509 99 00 66Forgive me, I saw this in the CF and wanted to review it because this
looked useful.
I cannot tell from your output what the differences would be vs. your
proposed date_trunc().
I was actually expecting columns: RowNum(), ts, date_bin, new
date_trunc()
Where you explained the differences (maybe using the row number).It appears your issue is the 36 Minutes. And it does beg the question
"Where is that coming from".Finally, I assume that: even if you could fix it by using "AT UTC" to do
the grouping, and then change it to 'Europe/Warsaw'...
That 36 minutes probably creeps back in.
It "Feels" like the wrong answer, considering the inputs.Finally... NOBODY Chimed in after you provided this evidence. Was this
accepted as proof, or was MORE expected?Kirk
1. date_bin works good if you do not have changed time zone (for example
from summer to winter time).date_bin simply adds constant interval - if you want to round your time to
3 hours, 6 hours or 12 hours then you have problem if you want to pass time
zone changing point, because some times you want to add interval without
lack hour or add interval with extra hour.original date_trunc works very good with DST problem, but has limited
granularity, you can't round timestamp to 5 min, 10 min, 30 min, 3 hours, 6
hours etc.My data_trunc version with interval as period argument is able to
correctly overcome the time points at which the change from summer to
winter time and vice versa occurred for custom periods.Additionally, it does not require specifying the origin time, which can
sometimes be very difficult to determine. You can't simple use '0001-01-01
00:00:00' because it is problematic for some timezones.Then it must be defined differently for each time zone. This complicates
queries when you work with different zones.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Again, I want to help you get this reviewed, accepted. But I cannot tell
the difference between date_bin()
and what you are proposing. You are "Describing" the problem as dealing
with "timezones".
From a previous post:
Robert Haas: In order for the patch to have a chance of being accepted,
we would
need to have a clear understanding of exactly how this patch is
different from the existing date_bin(). If we knew that, we could
decide either that (a) ...
We are both asking. SHOW us lines with date_bin() and your trunc_date()
function.
So we can see the differences, side by side.
I've never used date_bin(), but I am interested, because I've had to do 15
Minute intervals recently and it was "Wordy".
Kirk