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
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;
--
