truncating timestamps on arbitrary intervals

From 5e36c4c888c65e358d2f87d84b64bc14d52f2b39 Mon Sep 17 00:00:00 2001
From: David Fetter <david@fetter.org>
Date: Tue, 25 Feb 2020 23:49:35 -0800
Subject: [PATCH v2] Add date_trunc_interval(interval, timestamp)
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.24.1"

This is a multi-part message in MIME format.
--------------2.24.1
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


per John Naylor

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ceda48e0fc..3863c222a2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6949,6 +6949,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>2 days 03:00:00</literal></entry>
        </row>
 
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal></entry>
+        <entry><literal>2001-02-16 20:30:00</literal></entry>
+       </row>
+
        <row>
         <entry>
          <indexterm>
@@ -7818,7 +7827,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -7902,6 +7911,21 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0b6c9d5ea8..ed742592af 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -30,6 +30,7 @@
 #include "nodes/nodeFuncs.h"
 #include "nodes/supportnodes.h"
 #include "parser/scansup.h"
+#include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datetime.h"
@@ -3804,6 +3805,144 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *interval = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+	fsec_t		ifsec,
+				tfsec;
+	uint32_t	unit = 0,
+				popcount = 0;
+	enum		TimeUnit {
+					us     = 1 << 0,
+					ms     = 1 << 1,
+					second = 1 << 2,
+					minute = 1 << 3,
+					hour   = 1 << 4,
+					day    = 1 << 5,
+					month  = 1 << 6,
+					year   = 1 << 7
+				};
+
+	struct pg_tm it;
+	struct pg_tm tt;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (interval2tm(*interval, &it, &ifsec) != 0)
+		elog(ERROR, "could not convert interval to tm");
+
+	if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	if (it.tm_year != 0)
+	{
+		tt.tm_year = it.tm_year * (tt.tm_year / it.tm_year);
+		unit |= year;
+	}
+	if (it.tm_mon != 0)
+	{
+		tt.tm_mon = it.tm_mon * (tt.tm_mon / it.tm_mon);
+		unit |= month;
+	}
+	if (it.tm_mday != 0)
+	{
+		tt.tm_mday = it.tm_mday * (tt.tm_mday / it.tm_mday);
+		unit |= day;
+	}
+	if (it.tm_hour != 0)
+	{
+		tt.tm_hour = it.tm_hour * (tt.tm_hour / it.tm_hour);
+		unit |= hour;
+	}
+	if (it.tm_min != 0)
+	{
+		tt.tm_min = it.tm_min * (tt.tm_min / it.tm_min);
+		unit |= minute;
+	}
+	if (it.tm_sec != 0)
+	{
+		tt.tm_sec = it.tm_sec * (tt.tm_sec / it.tm_sec);
+		unit |= second;
+	}
+	if (ifsec > 0)
+	{
+		tfsec = ifsec * (tfsec / ifsec);
+
+		if (ifsec >= 1000)
+			unit |= ms;
+		else
+			unit |= us;
+	}
+	if (unit == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("interval not initialized")));
+
+	popcount = pg_popcount32(unit);
+
+	if ( popcount > 1 )
+		goto error;
+	/*
+	 * Justify all lower timestamp units and throw an error if any
+	 * of the lower interval units are non-zero.
+	 */
+	switch (unit)
+	{
+			case year:
+				tt.tm_mon = 1;
+				if (it.tm_mon != 0)
+					goto error;
+			case month:
+				tt.tm_mday = 1;
+				if (it.tm_mday != 0)
+					goto error;
+			case day:
+				tt.tm_hour = 0;
+				if (it.tm_hour != 0)
+					goto error;
+			case hour:
+				tt.tm_min = 0;
+				if (it.tm_min != 0)
+					goto error;
+			case minute:
+				tt.tm_sec = 0;
+				if (it.tm_sec != 0)
+					goto error;
+			case second:
+				tfsec = 0;
+			case ms:
+			case us:
+				break;
+			default:
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 errmsg("interval unit not supported")));
+
+	}
+
+	if (tm2timestamp(&tt, tfsec, NULL, &result) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	PG_RETURN_TIMESTAMP(result);
+
+error:
+	ereport(ERROR,
+			// WIP is there a better errcode?
+			(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+			 errmsg("only one interval unit allowed for truncation")));
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index eb3c1a88d1..0cec6c6799 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5660,6 +5660,9 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..174790e872 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,35 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('5 years'),
+  ('1 month'),
+  ('7 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 millisecond'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2004-02-29 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 5 years          | Sat Jan 01 00:00:00 2000
+ 1 month          | Sun Feb 01 00:00:00 2004
+ 7 days           | Sat Feb 28 00:00:00 2004
+ 2 hours          | Sun Feb 29 14:00:00 2004
+ 15 minutes       | Sun Feb 29 15:30:00 2004
+ 10 seconds       | Sun Feb 29 15:44:10 2004
+ 100 millisecond  | Sun Feb 29 15:44:17.7 2004
+ 250 microseconds | Sun Feb 29 15:44:17.71375 2004
+(8 rows)
+
+SELECT date_trunc_interval('1 year 1 ms'::interval, '2004-02-29 15:44:17.71393');
+ERROR:  only one interval unit allowed for truncation
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..f46c229f6a 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,24 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('5 years'),
+  ('1 month'),
+  ('7 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 millisecond'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2004-02-29 15:44:17.71393') ts (ts);
+
+SELECT date_trunc_interval('1 year 1 ms'::interval, '2004-02-29 15:44:17.71393');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

--------------2.24.1--

 doc/src/sgml/func.sgml                  |  26 ++++++-
 src/backend/utils/adt/timestamp.c       | 126 ++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   3 +
 src/test/regress/expected/timestamp.out |  35 +++++++++
 src/test/regress/sql/timestamp.sql      |  22 ++++++
 5 files changed, 211 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ceda48e0fc..3863c222a2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6949,6 +6949,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>2 days 03:00:00</literal></entry>
        </row>
 
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal></entry>
+        <entry><literal>2001-02-16 20:30:00</literal></entry>
+       </row>
+
        <row>
         <entry>
          <indexterm>
@@ -7818,7 +7827,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -7902,6 +7911,21 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0b6c9d5ea8..9613f86355 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -30,6 +30,7 @@
 #include "nodes/nodeFuncs.h"
 #include "nodes/supportnodes.h"
 #include "parser/scansup.h"
+#include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datetime.h"
@@ -3804,6 +3805,131 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *interval = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+	fsec_t		ifsec,
+				tfsec;
+	uint32		unit = 0;
+
+	struct pg_tm it;
+	struct pg_tm tt;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (interval2tm(*interval, &it, &ifsec) != 0)
+		elog(ERROR, "could not convert interval to tm");
+
+	if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	if (it.tm_year != 0)
+	{
+		tt.tm_year = it.tm_year * (tt.tm_year / it.tm_year);
+		unit |= DTK_M(DTK_YEAR);
+	}
+	if (it.tm_mon != 0)
+	{
+		if (it.tm_mon > tt.tm_mon)
+			tt.tm_mon = 1;
+		else
+			tt.tm_mon = it.tm_mon * (tt.tm_mon / it.tm_mon);
+
+		unit |= DTK_M(DTK_MONTH);
+	}
+	if (it.tm_mday != 0)
+	{
+		if (it.tm_mday > tt.tm_mday)
+			tt.tm_mday = 1;
+		else
+			tt.tm_mday = it.tm_mday * (tt.tm_mday / it.tm_mday);
+
+		unit |= DTK_M(DTK_DAY);
+	}
+	if (it.tm_hour != 0)
+	{
+		tt.tm_hour = it.tm_hour * (tt.tm_hour / it.tm_hour);
+		unit |= DTK_M(DTK_HOUR);
+	}
+	if (it.tm_min != 0)
+	{
+		tt.tm_min = it.tm_min * (tt.tm_min / it.tm_min);
+		unit |= DTK_M(DTK_MINUTE);
+	}
+	if (it.tm_sec != 0)
+	{
+		tt.tm_sec = it.tm_sec * (tt.tm_sec / it.tm_sec);
+		unit |= DTK_M(DTK_SECOND);
+	}
+	if (ifsec != 0)
+	{
+		tfsec = ifsec * (tfsec / ifsec);
+
+		if (ifsec >= 1000)
+			unit |= DTK_M(DTK_MILLISEC);
+		else
+			unit |= DTK_M(DTK_MICROSEC);
+	}
+
+	if (unit == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("interval not initialized")));
+	else
+	{
+		int popcount = pg_popcount32(unit);
+
+		if (popcount > 1)
+			ereport(ERROR,
+					// WIP is there a better errcode?
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("only one interval unit allowed for truncation")));
+	}
+
+	/*
+	 * Justify all lower timestamp units and throw an error if any
+	 * of the lower interval units are non-zero.
+	 */
+	switch (unit)
+	{
+			case DTK_M(DTK_YEAR):
+				tt.tm_mon = 1;
+			case DTK_M(DTK_MONTH):
+				tt.tm_mday = 1;
+			case DTK_M(DTK_DAY):
+				tt.tm_hour = 0;
+			case DTK_M(DTK_HOUR):
+				tt.tm_min = 0;
+			case DTK_M(DTK_MINUTE):
+				tt.tm_sec = 0;
+			case DTK_M(DTK_SECOND):
+				tfsec = 0;
+			case DTK_M(DTK_MILLISEC):
+			case DTK_M(DTK_MICROSEC):
+				break;
+			default:
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+						 errmsg("interval unit not supported")));
+	}
+
+	if (tm2timestamp(&tt, tfsec, NULL, &result) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0345118cdb..ddb1b84b52 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5660,6 +5660,9 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..1acb2e78a8 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,41 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- truncate on intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1 month'),
+  ('6 months'),
+  ('7 days'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 50 years         | Sat Jan 01 00:00:00 2000
+ 1 month          | Sat Feb 01 00:00:00 2020
+ 6 months         | Wed Jan 01 00:00:00 2020
+ 7 days           | Fri Feb 07 00:00:00 2020
+ 15 days          | Sat Feb 01 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(10 rows)
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+ERROR:  only one interval unit allowed for truncation
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..41485f6aa7 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,28 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- truncate on intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1 month'),
+  ('6 months'),
+  ('7 days'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

 doc/src/sgml/func.sgml                  |  26 ++++++-
 src/backend/utils/adt/timestamp.c       | 118 ++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   3 +
 src/test/regress/expected/timestamp.out |  44 ++++++++++++
 src/test/regress/sql/timestamp.sql      |  26 +++++++
 5 files changed, 216 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28035f1635..752cb13c0a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6949,6 +6949,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>2 days 03:00:00</literal></entry>
        </row>
 
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal></entry>
+        <entry><literal>2001-02-16 20:30:00</literal></entry>
+       </row>
+
        <row>
         <entry>
          <indexterm>
@@ -7818,7 +7827,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -7902,6 +7911,21 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0b6c9d5ea8..26678613e2 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -30,6 +30,7 @@
 #include "nodes/nodeFuncs.h"
 #include "nodes/supportnodes.h"
 #include "parser/scansup.h"
+#include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datetime.h"
@@ -3804,6 +3805,123 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *interval = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+	fsec_t		ifsec,
+				tfsec;
+	uint32		unit = 0;
+
+	struct pg_tm it;
+	struct pg_tm tt;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (interval2tm(*interval, &it, &ifsec) != 0)
+		elog(ERROR, "could not convert interval to tm");
+
+	if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	if (it.tm_year != 0)
+	{
+		if (tt.tm_year < 0)
+			tt.tm_year -= tt.tm_year % it.tm_year + it.tm_year;
+		else
+			tt.tm_year -= tt.tm_year % it.tm_year;
+		unit |= DTK_M(DTK_YEAR);
+	}
+	if (it.tm_mon != 0)
+	{
+		tt.tm_mon -= (tt.tm_mon - 1) % it.tm_mon;
+		unit |= DTK_M(DTK_MONTH);
+	}
+	if (it.tm_mday != 0)
+	{
+		tt.tm_mday -= (tt.tm_mday - 1) % it.tm_mday;
+		unit |= DTK_M(DTK_DAY);
+	}
+	if (it.tm_hour != 0)
+	{
+		tt.tm_hour -= tt.tm_hour % it.tm_hour;
+		unit |= DTK_M(DTK_HOUR);
+	}
+	if (it.tm_min != 0)
+	{
+		tt.tm_min -= tt.tm_min % it.tm_min;
+		unit |= DTK_M(DTK_MINUTE);
+	}
+	if (it.tm_sec != 0)
+	{
+		tt.tm_sec -= tt.tm_sec % it.tm_sec;
+		unit |= DTK_M(DTK_SECOND);
+	}
+	if (ifsec != 0)
+	{
+		tfsec -= tfsec % ifsec;
+		unit |= DTK_M(DTK_MICROSEC);
+	}
+
+	/* Justify all smaller timestamp units */
+	switch (unit)
+	{
+		case DTK_M(DTK_YEAR):
+			tt.tm_mon = 1;
+			/* FALL THRU */
+		case DTK_M(DTK_MONTH):
+			tt.tm_mday = 1;
+			/* FALL THRU */
+		case DTK_M(DTK_DAY):
+			tt.tm_hour = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_HOUR):
+			tt.tm_min = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_MINUTE):
+			tt.tm_sec = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_SECOND):
+			tfsec = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_MICROSEC):
+			break;
+		default:
+			if (unit == 0)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("interval not initialized")));
+			else
+			{
+				int popcount = pg_popcount32(unit);
+
+				if (popcount > 1)
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+							 errmsg("only one interval unit allowed for truncation")));
+				else
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+							 errmsg("interval unit not supported")));
+			}
+	}
+
+	if (tm2timestamp(&tt, tfsec, NULL, &result) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..01524fb15a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5663,6 +5663,9 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..acc6c4b75f 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,50 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- truncate timestamps on intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1 month'),
+  ('2 months'),
+  ('6 months'),
+  ('7 days'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 50 years         | Sat Jan 01 00:00:00 2000
+ 1 month          | Sat Feb 01 00:00:00 2020
+ 2 months         | Wed Jan 01 00:00:00 2020
+ 6 months         | Wed Jan 01 00:00:00 2020
+ 7 days           | Sat Feb 08 00:00:00 2020
+ 15 days          | Sat Feb 01 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(11 rows)
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, TIMESTAMP '0055-06-1 01:01:01.0 BC');
+     date_trunc_interval     
+-----------------------------
+ Mon Jan 01 00:00:00 0101 BC
+(1 row)
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+ERROR:  only one interval unit allowed for truncation
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..9e5d72de84 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,32 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- truncate timestamps on intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1 month'),
+  ('2 months'),
+  ('6 months'),
+  ('7 days'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, TIMESTAMP '0055-06-1 01:01:01.0 BC');
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

 doc/src/sgml/func.sgml                  |  26 +++++-
 src/backend/utils/adt/timestamp.c       | 151 ++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   8 ++
 src/test/regress/expected/timestamp.out |  44 ++++++++++
 src/test/regress/sql/timestamp.sql      |  26 ++++++
 5 files changed, 254 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 323366feb6..d518e85411 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6960,6 +6960,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>2 days 03:00:00</literal></entry>
        </row>
 
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal></entry>
+        <entry><literal>2001-02-16 20:30:00</literal></entry>
+       </row>
+
        <row>
         <entry>
          <indexterm>
@@ -7829,7 +7838,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -7913,6 +7922,21 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0b6c9d5ea8..2b76ca2c57 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -30,6 +30,7 @@
 #include "nodes/nodeFuncs.h"
 #include "nodes/supportnodes.h"
 #include "parser/scansup.h"
+#include "port/pg_bitutils.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/datetime.h"
@@ -3804,6 +3805,156 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+static Timestamp
+timestamp_trunc_interval_internal(Interval *stride,
+								  Timestamp timestamp,
+								  Timestamp origin)
+{
+	Timestamp	result;
+	fsec_t		ifsec,
+				tfsec;
+	uint32		unit = 0;
+
+	struct pg_tm it;
+	struct pg_tm tt;
+
+	if (interval2tm(*stride, &it, &ifsec) != 0)
+		elog(ERROR, "could not convert interval to tm");
+
+	if (timestamp2tm(timestamp - origin, NULL, &tt, &tfsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	if (it.tm_year != 0)
+	{
+		if (tt.tm_year < 0)
+			tt.tm_year -= tt.tm_year % it.tm_year + it.tm_year;
+		else
+			tt.tm_year -= tt.tm_year % it.tm_year;
+		unit |= DTK_M(DTK_YEAR);
+	}
+	if (it.tm_mon != 0)
+	{
+		tt.tm_mon -= (tt.tm_mon - 1) % it.tm_mon;
+		unit |= DTK_M(DTK_MONTH);
+	}
+	if (it.tm_mday != 0)
+	{
+		tt.tm_mday -= (tt.tm_mday - 1) % it.tm_mday;
+		unit |= DTK_M(DTK_DAY);
+	}
+	if (it.tm_hour != 0)
+	{
+		tt.tm_hour -= tt.tm_hour % it.tm_hour;
+		unit |= DTK_M(DTK_HOUR);
+	}
+	if (it.tm_min != 0)
+	{
+		tt.tm_min -= tt.tm_min % it.tm_min;
+		unit |= DTK_M(DTK_MINUTE);
+	}
+	if (it.tm_sec != 0)
+	{
+		tt.tm_sec -= tt.tm_sec % it.tm_sec;
+		unit |= DTK_M(DTK_SECOND);
+	}
+	if (ifsec != 0)
+	{
+		tfsec -= tfsec % ifsec;
+		unit |= DTK_M(DTK_MICROSEC);
+	}
+
+	/* Justify all smaller timestamp units */
+	switch (unit)
+	{
+		case DTK_M(DTK_YEAR):
+			tt.tm_mon = 1;
+			/* FALL THRU */
+		case DTK_M(DTK_MONTH):
+			tt.tm_mday = 1;
+			/* FALL THRU */
+		case DTK_M(DTK_DAY):
+			tt.tm_hour = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_HOUR):
+			tt.tm_min = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_MINUTE):
+			tt.tm_sec = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_SECOND):
+			tfsec = 0;
+			/* FALL THRU */
+		case DTK_M(DTK_MICROSEC):
+			break;
+		default:
+			if (unit == 0)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("interval not initialized")));
+			else
+			{
+				int popcount = pg_popcount32(unit);
+
+				if (popcount > 1)
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+							 errmsg("only one interval unit allowed for truncation")));
+				else
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+							 errmsg("interval unit not supported")));
+			}
+	}
+
+	if (tm2timestamp(&tt, tfsec, NULL, &result) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	return result + origin;
+}
+
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	result = timestamp_trunc_interval_internal(stride, timestamp, 0);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
+Datum
+timestamp_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	origin = PG_GETARG_TIMESTAMP(2);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("origin timestamp out of range")));
+
+	result = timestamp_trunc_interval_internal(stride, timestamp, origin);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..6216e64862 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5663,6 +5663,14 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
+{ oid => '8990',
+  descr => 'truncate timestamp to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp timestamp',
+  prosrc => 'timestamp_trunc_interval_origin' },
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..acc6c4b75f 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,50 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- truncate timestamps on intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1 month'),
+  ('2 months'),
+  ('6 months'),
+  ('7 days'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 50 years         | Sat Jan 01 00:00:00 2000
+ 1 month          | Sat Feb 01 00:00:00 2020
+ 2 months         | Wed Jan 01 00:00:00 2020
+ 6 months         | Wed Jan 01 00:00:00 2020
+ 7 days           | Sat Feb 08 00:00:00 2020
+ 15 days          | Sat Feb 01 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(11 rows)
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, TIMESTAMP '0055-06-1 01:01:01.0 BC');
+     date_trunc_interval     
+-----------------------------
+ Mon Jan 01 00:00:00 0101 BC
+(1 row)
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+ERROR:  only one interval unit allowed for truncation
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..9e5d72de84 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,32 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- truncate timestamps on intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1 month'),
+  ('2 months'),
+  ('6 months'),
+  ('7 days'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, TIMESTAMP '0055-06-1 01:01:01.0 BC');
+
+-- disallow multiple units (ms + us is an exception)
+SELECT date_trunc_interval('1 year 1 microsecond', TIMESTAMP '2001-02-16 20:38:40.123456');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

 doc/src/sgml/func.sgml                  |  38 +++++++++-
 src/backend/utils/adt/timestamp.c       | 121 ++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |   8 +++
 src/test/regress/expected/timestamp.out |  92 ++++++++++++++++++++++++
 src/test/regress/sql/timestamp.sql      |  51 ++++++++++++++
 5 files changed, 309 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 323366feb6..3bc3464cd0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6960,6 +6960,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>2 days 03:00:00</literal></entry>
        </row>
 
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type> <optional>, <type>timestamp</type></optional>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal></entry>
+        <entry><literal>2001-02-16 20:30:00</literal></entry>
+       </row>
+
        <row>
         <entry>
          <indexterm>
@@ -7829,7 +7838,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -7913,6 +7922,33 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
+   <para>
+    The boundaries of the interval to truncate on can be controlled by setting the optional origin parameter. If not specfied, the default origin is January 1st, 2001.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 years'::interval, TIMESTAMP '2020-02-01', TIMESTAMP '2012-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2017-01-01 00:00:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0b6c9d5ea8..1f72bfca1a 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3804,6 +3804,127 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+static Timestamp
+timestamp_trunc_interval_internal(Interval *stride,
+								  Timestamp timestamp,
+								  Timestamp origin)
+{
+	Timestamp	result,
+				tm_diff;
+	fsec_t		ofsec,
+				tfsec;
+	int 		origin_months,
+				tm_months,
+				month_diff;
+
+	struct pg_tm ot;
+	struct pg_tm tt;
+
+	/*
+	 * For strides measured in days or smaller units, do one simple calculation
+	 * on the time in microseconds.
+	 */
+	if (stride->month == 0)
+	{
+		tm_diff = timestamp - origin;
+		tm_diff -= tm_diff % (stride->day * USECS_PER_DAY + stride->time);
+		return origin + tm_diff;
+	}
+
+	/*
+	 * For strides measured in years and/or months, convert origin and input
+	 * timestamps to months.
+	 */
+	if (stride->day != 0 || stride->time != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("cannot mix year or month interval units with day units or smaller")));
+
+	if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	if (timestamp2tm(origin, NULL, &ot, &ofsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	origin_months = ot.tm_year * 12 + ot.tm_mon - 1;
+	tm_months = tt.tm_year * 12 + tt.tm_mon - 1;
+
+	/* do the truncation */
+	month_diff = tm_months - origin_months;
+	month_diff -= month_diff % stride->month;
+	tm_months = origin_months + month_diff;
+
+	/* make sure truncation happens in the right direction */
+	if (tt.tm_year < 0)
+		tm_months -= stride->month;
+
+	tt.tm_year = tm_months / 12;
+	tt.tm_mon = (tm_months % 12) + 1;
+
+	/* justify all smaller timestamp units */
+	tt.tm_mday = 1;
+	tt.tm_hour = 0;
+	tt.tm_min = 0;
+	tt.tm_sec = 0;
+	tfsec = 0;
+
+	if (tm2timestamp(&tt, tfsec, NULL, &result) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	return result;
+}
+
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	/*
+	 * The default origin is 2001-01-01, which matches date_trunc as far as
+	 * aligning on ISO weeks. This numeric value should match the internal
+	 * value of SELECT make_timestamp(2001, 1, 1, 0, 0, 0);
+	 */
+	result = timestamp_trunc_interval_internal(stride, timestamp,
+											   31622400000000);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
+Datum
+timestamp_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	origin = PG_GETARG_TIMESTAMP(2);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	result = timestamp_trunc_interval_internal(stride, timestamp, origin);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..6216e64862 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5663,6 +5663,14 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
+{ oid => '8990',
+  descr => 'truncate timestamp to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp timestamp',
+  prosrc => 'timestamp_trunc_interval_origin' },
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..d47901bb0c 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,98 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2004-02-29 15:44:17.71393') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 50 years         | Mon Jan 01 00:00:00 2001
+ 1.5 years        | Tue Jan 01 00:00:00 2019
+ 18 months        | Tue Jan 01 00:00:00 2019
+ 6 months         | Wed Jan 01 00:00:00 2020
+ 15 days          | Thu Feb 06 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(10 rows)
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, timestamp '0055-06-1 01:01:01.0 BC');
+     date_trunc_interval     
+-----------------------------
+ Tue Jan 01 00:00:00 0100 BC
+(1 row)
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+   date_trunc_interval    
+--------------------------
+ Sat Feb 01 00:57:30 2020
+(1 row)
+
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+   date_trunc_interval    
+--------------------------
+ Sun Jan 01 00:00:00 2017
+(1 row)
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+ERROR:  cannot mix year or month interval units with day units or smaller
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..55ce22ecde 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,57 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2004-02-29 15:44:17.71393') ts (ts);
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, timestamp '0055-06-1 01:01:01.0 BC');
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

 doc/src/sgml/func.sgml                  |  65 +++++-
 src/backend/utils/adt/timestamp.c       | 390 ++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         |  30 +++
 src/test/regress/expected/timestamp.out | 113 +++++++++
 src/test/regress/sql/timestamp.sql      |  60 +++++
 5 files changed, 657 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7a0bb0c70a..4e7cfe243e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6976,6 +6976,42 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>2 days 03:00:00</literal></entry>
        </row>
 
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified interval; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal></entry>
+        <entry><literal>2001-02-16 20:30:00</literal></entry>
+       </row>
+
+      <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified interval aligned with specified origin; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal></entry>
+        <entry><literal>2001-02-16 20:35:00</literal></entry>
+       </row>
+
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamptz</type>, <type>text</type>)</function></literal></entry>
+        <entry><type>timestamptz  </type></entry>
+        <entry>Truncate to specified interval in the specified time zone; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('6 hours', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney');</literal></entry>
+        <entry><literal>2001-02-16 19:00:00+00</literal></entry>
+       </row>
+
+      <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamptz</type>, <type>timestamptz</type>, <type>text</type>)</function></literal></entry>
+        <entry><type>timestamptz</type></entry>
+        <entry>Truncate to specified interval aligned with specified origin in the specified time zone; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('6 hours', timestamptz '2001-02-16 20:38:40+00', timestamptz '2001-02-16 01:00:00+00', 'Australia/Sydney');</literal></entry>
+        <entry><literal>2001-02-16 20:00:00+00</literal></entry>
+       </row>
+
        <row>
         <entry>
          <indexterm>
@@ -7845,7 +7881,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -7929,6 +7965,33 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
+   <para>
+    The boundaries of the interval to truncate on can be controlled by setting the optional origin parameter. If not specfied, the default origin is January 1st, 2001.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 years'::interval, TIMESTAMP '2020-02-01', TIMESTAMP '2012-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2017-01-01 00:00:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 4caffb5804..9d1f3a0e13 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -45,6 +45,13 @@
 
 #define SAMESIGN(a,b)	(((a) < 0) == ((b) < 0))
 
+/*
+ * The default origin is 2001-01-01, which matches date_trunc as far as
+ * aligning on ISO weeks. This numeric value should match the internal
+ * value of SELECT make_timestamp(2001, 1, 1, 0, 0, 0);
+ */
+#define DATE_TRUNC_DEFAULT_ORIGIN 31622400000000
+
 /* Set at postmaster start */
 TimestampTz PgStartTime;
 
@@ -3804,6 +3811,122 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+static Timestamp
+timestamp_trunc_interval_internal(Interval *stride,
+								  Timestamp timestamp,
+								  Timestamp origin)
+{
+	Timestamp	result,
+				tm_diff;
+	fsec_t		ofsec,
+				tfsec;
+	int 		origin_months,
+				tm_months,
+				month_diff;
+
+	struct pg_tm ot;
+	struct pg_tm tt;
+
+	/*
+	 * For strides measured in days or smaller units, do one simple calculation
+	 * on the time in microseconds.
+	 */
+	if (stride->month == 0)
+	{
+		tm_diff = timestamp - origin;
+		tm_diff -= tm_diff % (stride->day * USECS_PER_DAY + stride->time);
+		return origin + tm_diff;
+	}
+
+	/*
+	 * For strides measured in years and/or months, convert origin and input
+	 * timestamps to months.
+	 */
+	if (stride->day != 0 || stride->time != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("cannot mix year or month interval units with day units or smaller")));
+
+	if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	if (timestamp2tm(origin, NULL, &ot, &ofsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	origin_months = ot.tm_year * 12 + ot.tm_mon - 1;
+	tm_months = tt.tm_year * 12 + tt.tm_mon - 1;
+
+	/* do the truncation */
+	month_diff = tm_months - origin_months;
+	month_diff -= month_diff % stride->month;
+	tm_months = origin_months + month_diff;
+
+	/* make sure truncation happens in the right direction */
+	if (tt.tm_year < 0)
+		tm_months -= stride->month;
+
+	tt.tm_year = tm_months / 12;
+	tt.tm_mon = (tm_months % 12) + 1;
+
+	/* justify all smaller timestamp units */
+	tt.tm_mday = 1;
+	tt.tm_hour = 0;
+	tt.tm_min = 0;
+	tt.tm_sec = 0;
+	tfsec = 0;
+
+	if (tm2timestamp(&tt, tfsec, NULL, &result) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	return result;
+}
+
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	result = timestamp_trunc_interval_internal(stride, timestamp,
+											   DATE_TRUNC_DEFAULT_ORIGIN);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
+Datum
+timestamp_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	origin = PG_GETARG_TIMESTAMP(2);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	result = timestamp_trunc_interval_internal(stride, timestamp, origin);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
@@ -3938,6 +4061,106 @@ timestamp_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMP(result);
 }
 
+/*
+ * Common code for timestamptz_trunc_interval*()
+ *
+ * tzp identifies the zone to truncate with respect to.  We assume
+ * infinite timestamps have already been rejected.
+ */
+static TimestampTz
+timestamptz_trunc_interval_internal(Interval *stride,
+									TimestampTz timestamp,
+									pg_tz *tzp,
+									TimestampTz origin)
+{
+	TimestampTz	result,
+				tm_diff;
+	fsec_t		ofsec,
+				tfsec;
+	int 		origin_months,
+				tm_months,
+				month_diff,
+				tz;
+
+	struct pg_tm ot;
+	struct pg_tm tt;
+
+	/* Convert input and origin to pg_tm with timezone. */
+	if (timestamp2tm(timestamp, &tz, &tt, &tfsec, NULL, tzp) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	// XXX it seems like we should pass TZ here, but it doesn't work that way
+	if (timestamp2tm(origin, NULL, &ot, &ofsec, NULL, NULL) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	/*
+	 * For strides measured in days or smaller units, do one simple calculation
+	 * on the time in microseconds.
+	 */
+	if (stride->month == 0)
+	{
+		// XXX the origin is exactly as passed in
+
+		// got the idea from timestamp_zone() to just recovert to timestamp
+		// with null tz.
+		if (tm2timestamp(&tt, tfsec, NULL, &timestamp) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		tm_diff = timestamp - origin;
+		tm_diff -= tm_diff % (stride->day * USECS_PER_DAY + stride->time);
+
+		// XXX this seems mysterious, hopefully there's a more principled way
+		result = dt2local(origin + tm_diff, -tz);
+	}
+	else
+	{
+		if (stride->day != 0 || stride->time != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("cannot mix year or month interval units with day units or smaller")));
+
+		/*
+		 * For strides measured in years and/or months, convert origin and
+		 * input to purely months.
+		 */
+		origin_months = ot.tm_year * 12 + ot.tm_mon - 1;
+		tm_months = tt.tm_year * 12 + tt.tm_mon - 1;
+
+		/* do the truncation */
+		month_diff = tm_months - origin_months;
+		month_diff -= month_diff % stride->month;
+		tm_months = origin_months + month_diff;
+
+		/* make sure truncation happens in the right direction */
+		if (tt.tm_year < 0)
+			tm_months -= stride->month;
+
+		tt.tm_year = tm_months / 12;
+		tt.tm_mon = (tm_months % 12) + 1;
+
+		/* justify all smaller timestamp units */
+		tt.tm_mday = 1;
+		tt.tm_hour = 0;
+		tt.tm_min = 0;
+		tt.tm_sec = 0;
+		tfsec = 0;
+
+
+		if (tm2timestamp(&tt, tfsec, &tz, &result) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+	}
+
+	return result;
+}
+
 /*
  * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
  *
@@ -4085,6 +4308,52 @@ timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
 	return result;
 }
 
+/* timestamptz_trunc_interval()
+ * Truncate timestamptz to specified interval in session timezone.
+ */
+Datum
+timestamptz_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = 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_interval_internal(stride, timestamp,
+												 session_timezone,
+												 DATE_TRUNC_DEFAULT_ORIGIN);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/* timestamptz_trunc_interval_origin()
+ * Truncate timestamptz to specified interval in session timezone.
+ */
+Datum
+timestamptz_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	TimestampTz result;
+	TimestampTz	origin = PG_GETARG_TIMESTAMPTZ(2);
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 session_timezone,
+												 origin);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
 /* timestamptz_trunc()
  * Truncate timestamptz to specified units in session timezone.
  */
@@ -4103,6 +4372,127 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(result);
 }
 
+/* timestamptz_trunc_interval_zone()
+ * Truncate timestamptz to specified interval in specified timezone.
+ */
+Datum
+timestamptz_trunc_interval_zone(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	text	   *zone = PG_GETARG_TEXT_PP(2);
+	TimestampTz result;
+	char		tzname[TZ_STRLEN_MAX + 1];
+	char	   *lowzone;
+	int			type,
+				val;
+	pg_tz	   *tzp;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMPTZ(timestamp);
+
+	/*
+	 * Look up the requested timezone (see notes in timestamptz_zone()).
+	 */
+	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+	/* DecodeTimezoneAbbrev requires lowercase input */
+	lowzone = downcase_truncate_identifier(tzname,
+										   strlen(tzname),
+										   false);
+
+	type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+	if (type == TZ || type == DTZ)
+	{
+		/* fixed-offset abbreviation, get a pg_tz descriptor for that */
+		tzp = pg_tzset_offset(-val);
+	}
+	else if (type == DYNTZ)
+	{
+		/* dynamic-offset abbreviation, use its referenced timezone */
+	}
+	else
+	{
+		/* try it as a full zone name */
+		tzp = pg_tzset(tzname);
+		if (!tzp)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("time zone \"%s\" not recognized", tzname)));
+	}
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 tzp,
+												 DATE_TRUNC_DEFAULT_ORIGIN);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/* timestamptz_trunc_interval_origin_zone()
+ * Truncate timestamptz to specified interval in specified timezone,
+ * aligned to the specified origin.
+ */
+Datum
+timestamptz_trunc_interval_origin_zone(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	TimestampTz	origin = PG_GETARG_TIMESTAMPTZ(2);
+	text	   *zone = PG_GETARG_TEXT_PP(3);
+	TimestampTz result;
+	char		tzname[TZ_STRLEN_MAX + 1];
+	char	   *lowzone;
+	int			type,
+				val;
+	pg_tz	   *tzp;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	/*
+	 * Look up the requested timezone (see notes in timestamptz_zone()).
+	 */
+	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+	/* DecodeTimezoneAbbrev requires lowercase input */
+	lowzone = downcase_truncate_identifier(tzname,
+										   strlen(tzname),
+										   false);
+
+	type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+	if (type == TZ || type == DTZ)
+	{
+		/* fixed-offset abbreviation, get a pg_tz descriptor for that */
+		tzp = pg_tzset_offset(-val);
+	}
+	else if (type == DYNTZ)
+	{
+		/* dynamic-offset abbreviation, use its referenced timezone */
+	}
+	else
+	{
+		/* try it as a full zone name */
+		tzp = pg_tzset(tzname);
+		if (!tzp)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("time zone \"%s\" not recognized", tzname)));
+	}
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 tzp,
+												 origin);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
 /* timestamptz_trunc_zone()
  * Truncate timestamptz to specified units in specified timezone.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87d25d4a4b..e8aeb4801c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5663,6 +5663,36 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
+{ oid => '8990',
+  descr => 'truncate timestamp to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp timestamp',
+  prosrc => 'timestamp_trunc_interval_origin' },
+
+{ oid => '8991',
+  descr => 'truncate timestamp with time zone to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz', prosrc => 'timestamptz_trunc_interval' },
+{ oid => '8992',
+  descr => 'truncate timestamp with time zone to specified interval, in specified time zone',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz text',
+  prosrc => 'timestamptz_trunc_interval_zone' },
+
+{ oid => '8993',
+  descr => 'truncate timestamp with time zone to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz timestamptz', prosrc => 'timestamptz_trunc_interval_origin' },
+{ oid => '8994',
+  descr => 'truncate timestamp with time zone to specified interval and origin, in specified time zone',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz timestamptz text',
+  prosrc => 'timestamptz_trunc_interval_origin_zone' },
+
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..4a0577a38b 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,119 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2004-02-29 15:44:17.71393') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+SELECT date_trunc('hour', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney') =
+date_trunc_interval('1 hour', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney');
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT date_trunc('day', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney') =
+date_trunc_interval('1 day', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney');
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT date_trunc('month', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney') =
+date_trunc_interval('1 month', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney');
+ ?column? 
+----------
+ t
+(1 row)
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 50 years         | Mon Jan 01 00:00:00 2001
+ 1.5 years        | Tue Jan 01 00:00:00 2019
+ 18 months        | Tue Jan 01 00:00:00 2019
+ 6 months         | Wed Jan 01 00:00:00 2020
+ 15 days          | Thu Feb 06 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(10 rows)
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, timestamp '0055-06-1 01:01:01.0 BC');
+     date_trunc_interval     
+-----------------------------
+ Tue Jan 01 00:00:00 0100 BC
+(1 row)
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+   date_trunc_interval    
+--------------------------
+ Sat Feb 01 00:57:30 2020
+(1 row)
+
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+   date_trunc_interval    
+--------------------------
+ Sun Jan 01 00:00:00 2017
+(1 row)
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+ERROR:  cannot mix year or month interval units with day units or smaller
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..27be53112d 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,66 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2004-02-29 15:44:17.71393') ts (ts);
+
+SELECT date_trunc('hour', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney') =
+date_trunc_interval('1 hour', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney');
+
+SELECT date_trunc('day', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney') =
+date_trunc_interval('1 day', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney');
+
+SELECT date_trunc('month', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney') =
+date_trunc_interval('1 month', timestamp with time zone '2020-02-01 01:21:01+02', 'Australia/Sydney');
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- truncate BC timestamps on intervals
+SELECT date_trunc_interval('100 year'::interval, timestamp '0055-06-1 01:01:01.0 BC');
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

 doc/src/sgml/func.sgml                    |  65 ++++-
 src/backend/utils/adt/timestamp.c         | 440 ++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat           |  30 ++
 src/include/datatype/timestamp.h          |   8 +
 src/test/regress/expected/timestamp.out   | 209 ++++++++++++++
 src/test/regress/expected/timestamptz.out | 149 ++++++++++
 src/test/regress/sql/timestamp.sql        | 118 ++++++++
 src/test/regress/sql/timestamptz.sql      |  90 ++++++
 8 files changed, 1108 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7a0bb0c70a..68afdb9ee4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6976,6 +6976,42 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>2 days 03:00:00</literal></entry>
        </row>
 
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified interval; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal></entry>
+        <entry><literal>2001-02-16 20:30:00</literal></entry>
+       </row>
+
+      <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp</type>, <type>timestamp</type>)</function></literal></entry>
+        <entry><type>timestamp</type></entry>
+        <entry>Truncate to specified interval aligned with specified origin; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal></entry>
+        <entry><literal>2001-02-16 20:35:00</literal></entry>
+       </row>
+
+       <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp with time zone</type>, <optional><type>text</type></optional>)</function></literal></entry>
+        <entry><type>timestamp with time zone</type></entry>
+        <entry>Truncate to specified interval in the specified time zone; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('6 hours', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</literal></entry>
+        <entry><literal>2001-02-16 19:00:00+00</literal></entry>
+       </row>
+
+      <row>
+        <entry><literal><function>date_trunc_interval(<type>interval</type>, <type>timestamp with time zone</type>, <type>timestamp with time zone</type>, <optional><type>text</type></optional>)</function></literal></entry>
+        <entry><type>timestamp with time zone</type></entry>
+        <entry>Truncate to specified interval aligned with specified origin in the specified time zone; see <xref linkend="functions-datetime-trunc"/>
+        </entry>
+        <entry><literal>date_trunc_interval('6 hours', timestamptz '2001-02-16 20:38:40+00', timestamptz '2001-02-16 01:00:00+00', 'Australia/Sydney')</literal></entry>
+        <entry><literal>2001-02-16 20:00:00+00</literal></entry>
+       </row>
+
        <row>
         <entry>
          <indexterm>
@@ -7845,7 +7881,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -7929,6 +7965,33 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
+   <para>
+    The boundaries of the interval to truncate on can be controlled by setting the optional origin parameter. If not specfied, the default origin is January 1st, 2001.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 years'::interval, TIMESTAMP '2020-02-01', TIMESTAMP '2012-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2017-01-01 00:00:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 4caffb5804..a6af9217d5 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -45,6 +45,13 @@
 
 #define SAMESIGN(a,b)	(((a) < 0) == ((b) < 0))
 
+/*
+ * The default origin is 2001-01-01, which matches date_trunc as far as
+ * aligning on ISO weeks. This numeric value should match the internal
+ * value of SELECT make_timestamp(2001, 1, 1, 0, 0, 0);
+ */
+#define DATE_TRUNC_DEFAULT_ORIGIN 31622400000000
+
 /* Set at postmaster start */
 TimestampTz PgStartTime;
 
@@ -3804,6 +3811,146 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+static Timestamp
+timestamp_trunc_interval_internal(Interval *stride,
+								  Timestamp timestamp,
+								  Timestamp origin)
+{
+	Timestamp	result,
+				tm_diff,
+				tm_usecs,
+				tm_delta;
+	fsec_t		ofsec,
+				tfsec;
+	int 		origin_months,
+				tm_months,
+				result_months,
+				month_diff,
+				month_delta = 0;
+
+	struct pg_tm ot, /* origin */
+				 tt, /* input */
+				 rt; /* result */
+
+	tm_diff = timestamp - origin;
+
+	/*
+	 * For strides measured in days or smaller units, do one simple calculation
+	 * on the time in microseconds.
+	 */
+	if (stride->month == 0)
+	{
+		tm_usecs = stride->day * USECS_PER_DAY + stride->time;
+
+		/* trivial case of 1 usec */
+		if (tm_usecs == 1)
+			return timestamp;
+
+		tm_delta = tm_diff - tm_diff % tm_usecs;;
+		if (tm_diff < 0)
+			tm_delta -= tm_usecs;
+
+		result = origin + tm_delta;
+	}
+	else
+	{
+		/*
+		 * For strides measured in years and/or months, convert origin and
+		 * input timestamps to months.
+		 */
+		if (stride->day != 0 || stride->time != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("cannot mix year or month interval units with day units or smaller")));
+
+		if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		if (timestamp2tm(origin, NULL, &ot, &ofsec, NULL, NULL) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+
+		origin_months = ot.tm_year * 12 + ot.tm_mon - 1;
+		tm_months = tt.tm_year * 12 + tt.tm_mon - 1;
+		month_diff = tm_months - origin_months;
+
+		/* take the origin's smaller units into account */
+		if (Minor_Units(tt, tfsec) < Minor_Units(ot, ofsec))
+			month_diff--;
+
+		month_delta = month_diff - month_diff % stride->month;
+
+		/*
+		 * Make sure truncation happens in the right direction.
+		 * XXX This is a bit of a hack.
+		 */
+		if (month_diff < 0 && stride->month != 1)
+			month_delta -= stride->month;
+
+		result_months = origin_months + month_delta;
+
+		/* compute result fields of pg_tm struct */
+		rt.tm_year = result_months / 12;
+		rt.tm_mon = (result_months % 12) + 1;
+		/* align on origin's smaller units */
+		rt.tm_mday = ot.tm_mday;
+		rt.tm_hour = ot.tm_hour;
+		rt.tm_min = ot.tm_min;
+		rt.tm_sec = ot.tm_sec;
+
+		/* Note using the origin's fsec directly */
+		if (tm2timestamp(&rt, ofsec, NULL, &result) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+	}
+	return result;
+}
+
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	result = timestamp_trunc_interval_internal(stride, timestamp,
+											   DATE_TRUNC_DEFAULT_ORIGIN);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
+Datum
+timestamp_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	origin = PG_GETARG_TIMESTAMP(2);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	result = timestamp_trunc_interval_internal(stride, timestamp, origin);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
@@ -3938,6 +4085,132 @@ timestamp_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMP(result);
 }
 
+/*
+ * Common code for timestamptz_trunc_interval*()
+ *
+ * tzp identifies the zone to truncate with respect to.  We assume
+ * infinite timestamps have already been rejected.
+ */
+static TimestampTz
+timestamptz_trunc_interval_internal(Interval *stride,
+									TimestampTz timestamp,
+									pg_tz *tzp,
+									TimestampTz origin)
+{
+	TimestampTz	result,
+				tm_diff,
+				tm_usecs,
+				tm_delta;
+	fsec_t		ofsec,
+				tfsec;
+	int 		origin_months,
+				tm_months,
+				result_months,
+				month_diff,
+				month_delta = 0,
+				tz;
+
+	struct pg_tm ot, /* origin */
+				 tt, /* input */
+				 rt; /* result */
+
+	/* Convert input to pg_tm with timezone. */
+	if (timestamp2tm(timestamp, &tz, &tt, &tfsec, NULL, tzp) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	// XXX it seems like we should pass TZ here, but it seems to break
+	// things
+	if (timestamp2tm(origin, NULL, &ot, &ofsec, NULL, tzp) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	/*
+	 * For strides measured in days or smaller units, do one simple calculation
+	 * on the time in microseconds.
+	 */
+	if (stride->month == 0)
+	{
+		tm_usecs = stride->day * USECS_PER_DAY + stride->time;
+
+		/* trivial case of 1 usec */
+		if (tm_usecs == 1)
+			return timestamp;
+
+		// got the idea from timestamp_zone() to just recovert to timestamp
+		// with null tz.
+		if (tm2timestamp(&tt, tfsec, NULL, &timestamp) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		// WIP
+		// if (tm2timestamp(&ot, ofsec, NULL, &origin) != 0)
+		// 	ereport(ERROR,
+		// 			(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+		// 			 errmsg("timestamp out of range")));
+
+		tm_diff = timestamp - origin;
+
+		tm_delta = tm_diff - tm_diff % tm_usecs;;
+		if (tm_diff < 0)
+			tm_delta -= tm_usecs;
+
+
+		// XXX this seems mysterious, hopefully there's a more principled way
+		result = dt2local(origin + tm_delta, -tz);
+	}
+	else
+	{
+		/*
+		 * For strides measured in years and/or months, convert origin and
+		 * input timestamps to months.
+		 */
+		if (stride->day != 0 || stride->time != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("cannot mix year or month interval units with day units or smaller")));
+
+		origin_months = ot.tm_year * 12 + ot.tm_mon - 1;
+		tm_months = tt.tm_year * 12 + tt.tm_mon - 1;
+		month_diff = tm_months - origin_months;
+
+		/* take the origin's smaller units into account */
+		if (Minor_Units(tt, tfsec) < Minor_Units(ot, ofsec))
+			month_diff--;
+
+		month_delta = month_diff - month_diff % stride->month;
+
+		/*
+		 * Make sure truncation happens in the right direction.
+		 * XXX This is a bit of a hack.
+		 */
+		if (month_diff < 0 && stride->month != 1)
+			month_delta -= stride->month;
+
+		result_months = origin_months + month_delta;
+
+		/* compute result fields of pg_tm struct */
+		rt.tm_year = result_months / 12;
+		rt.tm_mon = (result_months % 12) + 1;
+		/* align on origin's smaller units */
+		rt.tm_mday = ot.tm_mday;
+		rt.tm_hour = ot.tm_hour;
+		rt.tm_min = ot.tm_min;
+		rt.tm_sec = ot.tm_sec;
+
+		/* Note using the origin's fsec directly */
+		if (tm2timestamp(&rt, ofsec, &tz, &result) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+	}
+
+	return result;
+}
+
 /*
  * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
  *
@@ -4085,6 +4358,52 @@ timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
 	return result;
 }
 
+/* timestamptz_trunc_interval()
+ * Truncate timestamptz to specified interval in session timezone.
+ */
+Datum
+timestamptz_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = 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_interval_internal(stride, timestamp,
+												 session_timezone,
+												 DATE_TRUNC_DEFAULT_ORIGIN);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/* timestamptz_trunc_interval_origin()
+ * Truncate timestamptz to specified interval in session timezone.
+ */
+Datum
+timestamptz_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	TimestampTz result;
+	TimestampTz	origin = PG_GETARG_TIMESTAMPTZ(2);
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 session_timezone,
+												 origin);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
 /* timestamptz_trunc()
  * Truncate timestamptz to specified units in session timezone.
  */
@@ -4103,6 +4422,127 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(result);
 }
 
+/* timestamptz_trunc_interval_zone()
+ * Truncate timestamptz to specified interval in specified timezone.
+ */
+Datum
+timestamptz_trunc_interval_zone(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	text	   *zone = PG_GETARG_TEXT_PP(2);
+	TimestampTz result;
+	char		tzname[TZ_STRLEN_MAX + 1];
+	char	   *lowzone;
+	int			type,
+				val;
+	pg_tz	   *tzp;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMPTZ(timestamp);
+
+	/*
+	 * Look up the requested timezone (see notes in timestamptz_zone()).
+	 */
+	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+	/* DecodeTimezoneAbbrev requires lowercase input */
+	lowzone = downcase_truncate_identifier(tzname,
+										   strlen(tzname),
+										   false);
+
+	type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+	if (type == TZ || type == DTZ)
+	{
+		/* fixed-offset abbreviation, get a pg_tz descriptor for that */
+		tzp = pg_tzset_offset(-val);
+	}
+	else if (type == DYNTZ)
+	{
+		/* dynamic-offset abbreviation, use its referenced timezone */
+	}
+	else
+	{
+		/* try it as a full zone name */
+		tzp = pg_tzset(tzname);
+		if (!tzp)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("time zone \"%s\" not recognized", tzname)));
+	}
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 tzp,
+												 DATE_TRUNC_DEFAULT_ORIGIN);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/* timestamptz_trunc_interval_origin_zone()
+ * Truncate timestamptz to specified interval in specified timezone,
+ * aligned to the specified origin.
+ */
+Datum
+timestamptz_trunc_interval_origin_zone(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	TimestampTz	origin = PG_GETARG_TIMESTAMPTZ(2);
+	text	   *zone = PG_GETARG_TEXT_PP(3);
+	TimestampTz result;
+	char		tzname[TZ_STRLEN_MAX + 1];
+	char	   *lowzone;
+	int			type,
+				val;
+	pg_tz	   *tzp;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	/*
+	 * Look up the requested timezone (see notes in timestamptz_zone()).
+	 */
+	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+	/* DecodeTimezoneAbbrev requires lowercase input */
+	lowzone = downcase_truncate_identifier(tzname,
+										   strlen(tzname),
+										   false);
+
+	type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+	if (type == TZ || type == DTZ)
+	{
+		/* fixed-offset abbreviation, get a pg_tz descriptor for that */
+		tzp = pg_tzset_offset(-val);
+	}
+	else if (type == DYNTZ)
+	{
+		/* dynamic-offset abbreviation, use its referenced timezone */
+	}
+	else
+	{
+		/* try it as a full zone name */
+		tzp = pg_tzset(tzname);
+		if (!tzp)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("time zone \"%s\" not recognized", tzname)));
+	}
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 tzp,
+												 origin);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
 /* timestamptz_trunc_zone()
  * Truncate timestamptz to specified units in specified timezone.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87d25d4a4b..e8aeb4801c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5663,6 +5663,36 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
+{ oid => '8990',
+  descr => 'truncate timestamp to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp timestamp',
+  prosrc => 'timestamp_trunc_interval_origin' },
+
+{ oid => '8991',
+  descr => 'truncate timestamp with time zone to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz', prosrc => 'timestamptz_trunc_interval' },
+{ oid => '8992',
+  descr => 'truncate timestamp with time zone to specified interval, in specified time zone',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz text',
+  prosrc => 'timestamptz_trunc_interval_zone' },
+
+{ oid => '8993',
+  descr => 'truncate timestamp with time zone to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz timestamptz', prosrc => 'timestamptz_trunc_interval_origin' },
+{ oid => '8994',
+  descr => 'truncate timestamp with time zone to specified interval and origin, in specified time zone',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz timestamptz text',
+  prosrc => 'timestamptz_trunc_interval_origin_zone' },
+
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 6be6d35d1e..2f897b7575 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -93,6 +93,14 @@ typedef struct
 #define USECS_PER_MINUTE INT64CONST(60000000)
 #define USECS_PER_SEC	INT64CONST(1000000)
 
+/* compute total of non-month, non-year units in a pg_tm struct + fsec */
+#define Minor_Units(tm, usec) \
+	(tm.tm_mday - 1) * USECS_PER_DAY + \
+	tm.tm_hour * USECS_PER_HOUR + \
+	tm.tm_min * USECS_PER_MINUTE + \
+	tm.tm_sec * USECS_PER_SEC + \
+	usec
+
 /*
  * We allow numeric timezone offsets up to 15:59:59 either way from Greenwich.
  * Currently, the record holders for wackiest offsets in actual use are zones
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..2fd9deae62 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,215 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2020-02-29 15:44:17.71393') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-10 15:44:17.71393 BC') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '1999-12-31 15:44:17.71393') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-07 15:44:17.71393 BC') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 50 years         | Mon Jan 01 00:00:00 2001
+ 1.5 years        | Tue Jan 01 00:00:00 2019
+ 18 months        | Tue Jan 01 00:00:00 2019
+ 6 months         | Wed Jan 01 00:00:00 2020
+ 15 days          | Thu Feb 06 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(10 rows)
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+   date_trunc_interval    
+--------------------------
+ Sat Feb 01 00:57:30 2020
+(1 row)
+
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+   date_trunc_interval    
+--------------------------
+ Sun Jan 01 00:00:00 2017
+(1 row)
+
+SELECT date_trunc_interval('3 year', timestamp '2015-01-14 20:38:40', timestamp '2012-01-15 01:01:01.123');
+     date_trunc_interval      
+------------------------------
+ Sun Jan 15 01:01:01.123 2012
+(1 row)
+
+SELECT date_trunc_interval('3 year', timestamp '2015-01-15 20:38:40', timestamp '2012-01-15 01:01:01.123');
+     date_trunc_interval      
+------------------------------
+ Thu Jan 15 01:01:01.123 2015
+(1 row)
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+ERROR:  cannot mix year or month interval units with day units or smaller
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 639b50308e..c2f3a55e62 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -663,6 +663,155 @@ SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-
                   | Thu Feb 15 20:00:00 2001 PST
 (1 row)
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '2020-02-29 15:44:17.71393+00') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, timestamptz '2000-01-01+00 BC', 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '0055-6-10 15:44:17.71393+00 BC') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '1999-12-31 15:44:17.71393+00') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '0055-6-07 15:44:17.71393+00 BC') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..d171bf95ec 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,124 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2020-02-29 15:44:17.71393') ts (ts);
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-10 15:44:17.71393 BC') ts (ts);
+
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '1999-12-31 15:44:17.71393') ts (ts);
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-07 15:44:17.71393 BC') ts (ts);
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+SELECT date_trunc_interval('3 year', timestamp '2015-01-14 20:38:40', timestamp '2012-01-15 01:01:01.123');
+SELECT date_trunc_interval('3 year', timestamp '2015-01-15 20:38:40', timestamp '2012-01-15 01:01:01.123');
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 300302dafd..f25451fd0f 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -193,6 +193,96 @@ SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-
 SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'GMT') as gmt_trunc;  -- fixed-offset abbreviation
 SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET') as vet_trunc;  -- variable-offset abbreviation
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '2020-02-29 15:44:17.71393+00') ts (ts);
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, timestamptz '2000-01-01+00 BC', 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '0055-6-10 15:44:17.71393+00 BC') ts (ts);
+
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '1999-12-31 15:44:17.71393+00') ts (ts);
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '0055-6-07 15:44:17.71393+00 BC') ts (ts);
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL

 doc/src/sgml/func.sgml                    |  85 ++++++-
 src/backend/utils/adt/timestamp.c         | 403 ++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat           |  30 +++
 src/test/regress/expected/timestamp.out   | 129 ++++++++++
 src/test/regress/expected/timestamptz.out |  38 +++
 src/test/regress/sql/timestamp.sql        |  73 ++++++
 src/test/regress/sql/timestamptz.sql      |  23 ++
 7 files changed, 780 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7119f0b2ca..b65921cc4b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8690,6 +8690,62 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         </para></entry>
        </row>
 
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <function>date_trunc_interval</function> ( <type>interval</type>, <type>timestamp</type> )
+         <returnvalue>timestamp</returnvalue>
+        </para>
+        <para>
+         Truncate to specified interval; see <xref linkend="functions-datetime-trunc"/>
+        </para>
+        <para>
+         <literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40')</literal>
+         <returnvalue>2001-02-16 20:30:00</returnvalue>
+        </para></entry>
+       </row>
+
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <function>date_trunc_interval</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> )
+         <returnvalue>timestamp</returnvalue>
+        </para>
+        <para>
+         Truncate to specified interval aligned with specified origin; see <xref linkend="functions-datetime-trunc"/>
+        </para>
+        <para>
+         <literal>date_trunc_interval('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal>
+         <returnvalue>2001-02-16 20:35:00</returnvalue>
+        </para></entry>
+       </row>
+
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <function>date_trunc_interval</function> ( <type>interval</type>, <type>timestamp with time zone</type> <optional>, <type>text</type></optional> )
+         <returnvalue>timestamp with time zone</returnvalue>
+        </para>
+        <para>
+         Truncate to specified interval in the specified time zone; see <xref linkend="functions-datetime-trunc"/>
+        </para>
+        <para>
+         <literal>date_trunc_interval('6 hours', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</literal>
+         <returnvalue>2001-02-16 19:00:00+00</returnvalue>
+        </para></entry>
+       </row>
+
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <function>date_trunc_interval</function> ( <type>interval</type>, <type>timestamp with time zone</type>, <type>timestamp with time zone</type> <optional>, <type>text</type></optional> )
+         <returnvalue>timestamp with time zone</returnvalue>
+        </para>
+        <para>
+         Truncate to specified interval aligned with specified origin in the specified time zone; see <xref linkend="functions-datetime-trunc"/>
+        </para>
+        <para>
+         <literal>date_trunc_interval('6 hours', timestamptz '2001-02-16 20:38:40+00', timestamptz '2001-02-16 01:00:00+00', 'Australia/Sydney')</literal>
+         <returnvalue>2001-02-16 20:00:00+00</returnvalue>
+        </para></entry>
+       </row>
+
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          <indexterm>
@@ -9641,7 +9697,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
   </sect2>
 
   <sect2 id="functions-datetime-trunc">
-   <title><function>date_trunc</function></title>
+   <title><function>date_trunc</function>, <function>date_trunc_interval</function></title>
 
    <indexterm>
     <primary>date_trunc</primary>
@@ -9725,6 +9781,33 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
 <lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
 </screen>
    </para>
+
+   <para>
+    The function <function>date_trunc_interval</function> is 
+    similar to the <function>date_trunc</function>, except that it
+    truncates to an arbitrary interval.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 minutes', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:35:00</computeroutput>
+</screen>
+   </para>
+
+   <para>
+    The boundaries of the interval to truncate on can be controlled by setting the optional origin parameter. If not specfied, the default origin is January 1st, 2001.
+   </para>
+
+   <para>
+    Example:
+<screen>
+SELECT date_trunc_interval('5 years'::interval, TIMESTAMP '2020-02-01', TIMESTAMP '2012-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2017-01-01 00:00:00</computeroutput>
+</screen>
+   </para>
+
   </sect2>
 
   <sect2 id="functions-datetime-zoneconvert">
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 5fe304cea7..f83ffe0b2a 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -46,6 +46,13 @@
 
 #define SAMESIGN(a,b)	(((a) < 0) == ((b) < 0))
 
+/*
+ * The default origin for date_trunc_origin, which matches date_trunc as
+ * far as aligning on ISO weeks, centuries, and millenia.  The value is
+ * computed from the number of days before POSTGRES EPOCH DATE zero.
+ */
+#define DATE_0001_01_01 (-730119 * USECS_PER_DAY)
+
 /* Set at postmaster start */
 TimestampTz PgStartTime;
 
@@ -3797,6 +3804,133 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+static Timestamp
+timestamp_trunc_interval_internal(Interval *stride,
+								  Timestamp timestamp,
+								  Timestamp origin)
+{
+	Timestamp	result,
+				tm_diff,
+				stride_usecs,
+				tm_delta;
+	fsec_t		ofsec,
+				tfsec;
+	int			yymm_result,
+				month_diff,
+				yymm_delta;
+
+	struct pg_tm ot, /* origin */
+				 tt, /* input */
+				 rt = {0}; /* result */
+
+	/*
+	 * For strides measured in days or smaller units, do one simple calculation
+	 * on the time in microseconds.
+	 */
+	if (stride->month == 0)
+	{
+		stride_usecs = stride->day * USECS_PER_DAY + stride->time;
+
+		/* trivial case of 1 usec */
+		if (stride_usecs == 1)
+			return timestamp;
+
+		tm_diff = timestamp - origin;
+		tm_delta = tm_diff - tm_diff % stride_usecs;;
+
+		result = origin + tm_delta;
+	}
+	else
+	{
+		if (stride->day != 0 || stride->time != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("cannot mix year or month interval units with day units or smaller")));
+
+		/*
+		 * For strides measured in years and/or months, convert origin and
+		 * input timestamps to pg_tm.
+		 */
+		if (timestamp2tm(timestamp, NULL, &tt, &tfsec, NULL, NULL) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		if (timestamp2tm(origin, NULL, &ot, &ofsec, NULL, NULL) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		/* require origin to align on month */
+		if (ot.tm_mday != 1 ||
+			ot.tm_hour != 0 ||
+			ot.tm_min != 0 ||
+			ot.tm_sec != 0 ||
+			ofsec != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("when truncating on months or years, origin must be aligned on the month")));
+
+		/* compute difference in months */
+		month_diff = tt.tm_mon - ot.tm_mon;
+		yymm_delta = 12 * (tt.tm_year - ot.tm_year) + month_diff;
+		yymm_delta -= yymm_delta % stride->month;
+
+		/* compute fields of result pg_tm struct */
+		yymm_result = ot.tm_year * 12 + ot.tm_mon - 1 + yymm_delta;
+		rt.tm_year = yymm_result / 12;
+		rt.tm_mon = (yymm_result % 12) + 1;
+		rt.tm_mday = 1;
+
+		if (tm2timestamp(&rt, 0, NULL, &result) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+	}
+
+	return result;
+}
+
+/* timestamp_trunc_interval()
+ * Truncate timestamp to specified interval.
+ */
+Datum
+timestamp_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	result = timestamp_trunc_interval_internal(stride, timestamp,
+											   DATE_0001_01_01);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
+Datum
+timestamp_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	origin = PG_GETARG_TIMESTAMP(2);
+	Timestamp	result;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	result = timestamp_trunc_interval_internal(stride, timestamp, origin);
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
@@ -3931,6 +4065,108 @@ timestamp_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMP(result);
 }
 
+/*
+ * Common code for timestamptz_trunc_interval*()
+ *
+ * tzp identifies the zone to truncate with respect to.  We assume
+ * infinite timestamps have already been rejected.
+ */
+static TimestampTz
+timestamptz_trunc_interval_internal(Interval *stride,
+									TimestampTz timestamp,
+									pg_tz *tzp,
+									TimestampTz origin)
+{
+	TimestampTz	result,
+				tm_diff,
+				stride_usecs,
+				tm_delta;
+	fsec_t		ofsec,
+				tfsec;
+	int			yymm_result,
+				month_diff,
+				yymm_delta,
+				tz;
+
+	struct pg_tm ot, /* origin */
+				 tt, /* input */
+				 rt = {0}; /* result */
+
+	/* Convert input to pg_tm with timezone. */
+	if (timestamp2tm(timestamp, &tz, &tt, &tfsec, NULL, tzp) != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	/*
+	 * For strides measured in days or smaller units, do one simple calculation
+	 * on the time in microseconds.
+	 */
+	if (stride->month == 0)
+	{
+		stride_usecs = stride->day * USECS_PER_DAY + stride->time;
+
+		/* trivial case of 1 usec */
+		if (stride_usecs == 1)
+			return timestamp;
+
+		// Recovert to timestamp with null tz. See timestamp_zone().
+		if (tm2timestamp(&tt, tfsec, NULL, &timestamp) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		tm_diff = timestamp - origin;
+		tm_delta = tm_diff - tm_diff % stride_usecs;;
+
+		result = dt2local(origin + tm_delta, -tz);
+	}
+	else
+	{
+		if (stride->day != 0 || stride->time != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("cannot mix year or month interval units with day units or smaller")));
+
+		/* don't apply timezone to the origin */
+		if (timestamp2tm(origin, NULL, &ot, &ofsec, NULL, NULL) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		/* require origin to align on month */
+		if (ot.tm_mday != 1 ||
+			ot.tm_hour != 0 ||
+			ot.tm_min != 0 ||
+			ot.tm_sec != 0 ||
+			ofsec != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("when truncating on months or years, origin must be aligned on the month")));
+
+		/* compute difference in months */
+		month_diff = tt.tm_mon - ot.tm_mon;
+		yymm_delta = 12 * (tt.tm_year - ot.tm_year) + month_diff;
+		yymm_delta -= yymm_delta % stride->month;
+
+		/* compute fields of result pg_tm struct */
+		yymm_result = ot.tm_year * 12 + ot.tm_mon - 1 + yymm_delta;
+		rt.tm_year = yymm_result / 12;
+		rt.tm_mon = (yymm_result % 12) + 1;
+		rt.tm_mday = 1;
+
+		/* write tz and dst to result struct */
+		tz = DetermineTimeZoneOffset(&rt, tzp);
+
+		if (tm2timestamp(&rt, 0, &tz, &result) != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+	}
+
+	return result;
+}
+
 /*
  * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
  *
@@ -4078,6 +4314,52 @@ timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
 	return result;
 }
 
+/* timestamptz_trunc_interval()
+ * Truncate timestamptz to specified interval in session timezone.
+ */
+Datum
+timestamptz_trunc_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = 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_interval_internal(stride, timestamp,
+												 session_timezone,
+												 DATE_0001_01_01);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/* timestamptz_trunc_interval_origin()
+ * Truncate timestamptz to specified interval in session timezone.
+ */
+Datum
+timestamptz_trunc_interval_origin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	TimestampTz result;
+	TimestampTz	origin = PG_GETARG_TIMESTAMPTZ(2);
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 session_timezone,
+												 origin);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
 /* timestamptz_trunc()
  * Truncate timestamptz to specified units in session timezone.
  */
@@ -4096,6 +4378,127 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(result);
 }
 
+/* timestamptz_trunc_interval_zone()
+ * Truncate timestamptz to specified interval in specified timezone.
+ */
+Datum
+timestamptz_trunc_interval_zone(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	text	   *zone = PG_GETARG_TEXT_PP(2);
+	TimestampTz result;
+	char		tzname[TZ_STRLEN_MAX + 1];
+	char	   *lowzone;
+	int			type,
+				val;
+	pg_tz	   *tzp;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMPTZ(timestamp);
+
+	/*
+	 * Look up the requested timezone (see notes in timestamptz_zone()).
+	 */
+	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+	/* DecodeTimezoneAbbrev requires lowercase input */
+	lowzone = downcase_truncate_identifier(tzname,
+										   strlen(tzname),
+										   false);
+
+	type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+	if (type == TZ || type == DTZ)
+	{
+		/* fixed-offset abbreviation, get a pg_tz descriptor for that */
+		tzp = pg_tzset_offset(-val);
+	}
+	else if (type == DYNTZ)
+	{
+		/* dynamic-offset abbreviation, use its referenced timezone */
+	}
+	else
+	{
+		/* try it as a full zone name */
+		tzp = pg_tzset(tzname);
+		if (!tzp)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("time zone \"%s\" not recognized", tzname)));
+	}
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 tzp,
+												 DATE_0001_01_01);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/* timestamptz_trunc_interval_origin_zone()
+ * Truncate timestamptz to specified interval in specified timezone,
+ * aligned to the specified origin.
+ */
+Datum
+timestamptz_trunc_interval_origin_zone(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	TimestampTz	origin = PG_GETARG_TIMESTAMPTZ(2);
+	text	   *zone = PG_GETARG_TEXT_PP(3);
+	TimestampTz result;
+	char		tzname[TZ_STRLEN_MAX + 1];
+	char	   *lowzone;
+	int			type,
+				val;
+	pg_tz	   *tzp;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	/*
+	 * Look up the requested timezone (see notes in timestamptz_zone()).
+	 */
+	text_to_cstring_buffer(zone, tzname, sizeof(tzname));
+
+	/* DecodeTimezoneAbbrev requires lowercase input */
+	lowzone = downcase_truncate_identifier(tzname,
+										   strlen(tzname),
+										   false);
+
+	type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
+
+	if (type == TZ || type == DTZ)
+	{
+		/* fixed-offset abbreviation, get a pg_tz descriptor for that */
+		tzp = pg_tzset_offset(-val);
+	}
+	else if (type == DYNTZ)
+	{
+		/* dynamic-offset abbreviation, use its referenced timezone */
+	}
+	else
+	{
+		/* try it as a full zone name */
+		tzp = pg_tzset(tzname);
+		if (!tzp)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("time zone \"%s\" not recognized", tzname)));
+	}
+
+	result = timestamptz_trunc_interval_internal(stride, timestamp,
+												 tzp,
+												 origin);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
 /* timestamptz_trunc_zone()
  * Truncate timestamptz to specified units in specified timezone.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 61f2c2f5b4..e59a3cefad 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5717,6 +5717,36 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+
+{ oid => '8989', descr => 'truncate timestamp to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp', prosrc => 'timestamp_trunc_interval' },
+{ oid => '8990',
+  descr => 'truncate timestamp to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp timestamp',
+  prosrc => 'timestamp_trunc_interval_origin' },
+
+{ oid => '8991',
+  descr => 'truncate timestamp with time zone to specified interval',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz', prosrc => 'timestamptz_trunc_interval' },
+{ oid => '8992',
+  descr => 'truncate timestamp with time zone to specified interval, in specified time zone',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz text',
+  prosrc => 'timestamptz_trunc_interval_zone' },
+
+{ oid => '8993',
+  descr => 'truncate timestamp with time zone to specified interval and origin',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz timestamptz', prosrc => 'timestamptz_trunc_interval_origin' },
+{ oid => '8994',
+  descr => 'truncate timestamp with time zone to specified interval and origin, in specified time zone',
+  proname => 'date_trunc_interval', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz timestamptz text',
+  prosrc => 'timestamptz_trunc_interval_origin_zone' },
+
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 5f97505a30..d8a1952582 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,135 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17
                  | Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2020-02-29 15:44:17.71393') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-10 15:44:17.71393 BC') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |      date_trunc_interval       
+------------------+--------------------------------
+ 50 years         | Mon Jan 01 00:00:00 2001
+ 1.5 years        | Wed Jan 01 00:00:00 2020
+ 18 months        | Wed Jan 01 00:00:00 2020
+ 6 months         | Wed Jan 01 00:00:00 2020
+ 15 days          | Thu Feb 06 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(10 rows)
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+   date_trunc_interval    
+--------------------------
+ Sat Feb 01 00:57:30 2020
+(1 row)
+
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+   date_trunc_interval    
+--------------------------
+ Sun Jan 01 00:00:00 2017
+(1 row)
+
+SELECT date_trunc_interval('3 year', timestamp '2015-01-14 20:38:40', timestamp '2012-01-01');
+   date_trunc_interval    
+--------------------------
+ Thu Jan 01 00:00:00 2015
+(1 row)
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+ERROR:  cannot mix year or month interval units with day units or smaller
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 639b50308e..6a3bd5745d 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -663,6 +663,44 @@ SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-
                   | Thu Feb 15 20:00:00 2001 PST
 (1 row)
 
+-- verify date_trunc_interval behaves the same as date_trunc for relevant
+-- inputs (excluding decade)
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '2020-02-29 15:44:17.71393+00') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ millennium  | 1000 y   | t
+ century     | 100 y    | t
+ year        | 1 y      | t
+ quarter     | 3 mon    | t
+ month       | 1 mon    | t
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(12 rows)
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..294ed85949 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,79 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- verify date_trunc_interval behaves the same as date_trunc (excluding decade)
+
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts) AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2020-02-29 15:44:17.71393') ts (ts);
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_trunc_interval(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-10 15:44:17.71393 BC') ts (ts);
+
+-- truncate timestamps on arbitrary intervals
+SELECT
+  interval,
+  date_trunc_interval(interval::interval, ts)
+FROM (
+  VALUES
+  ('50 years'),
+  ('1.5 years'),
+  ('18 months'),
+  ('6 months'),
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT TIMESTAMP '2020-02-11 15:44:17.71393') ts (ts);
+
+-- shift bins using the origin parameter:
+SELECT date_trunc_interval('5 min'::interval, timestamp '2020-02-1 01:01:01', timestamp '2020-02-01 00:02:30');
+SELECT date_trunc_interval('5 years'::interval, timestamp '2020-02-1 01:01:01', timestamp '2012-01-01');
+SELECT date_trunc_interval('3 year', timestamp '2015-01-14 20:38:40', timestamp '2012-01-01');
+
+-- not defined
+SELECT date_trunc_interval('1 month 1 day', timestamp '2001-02-16 20:38:40.123456');
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 300302dafd..3fa95eea8f 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -193,6 +193,29 @@ SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-
 SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'GMT') as gmt_trunc;  -- fixed-offset abbreviation
 SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'VET') as vet_trunc;  -- variable-offset abbreviation
 
+-- verify date_trunc_interval behaves the same as date_trunc for relevant
+-- inputs (excluding decade)
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts, 'Australia/Sydney') = date_trunc_interval(interval::interval, ts, 'Australia/Sydney') AS equal
+FROM (
+  VALUES
+  ('millennium', '1000 y'),
+  ('century', '100 y'),
+  ('year', '1 y'),
+  ('quarter', '3 mon'),
+  ('month', '1 mon'),
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '2020-02-29 15:44:17.71393+00') ts (ts);
+
 -- Test casting within a BETWEEN qualifier
 SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL

 doc/src/sgml/func.sgml                    | 14 +++++
 src/backend/utils/adt/timestamp.c         | 82 ++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat           | 11 ++++
 src/test/regress/expected/timestamp.out   | 91 +++++++++++++++++++++++++++++++
 src/test/regress/expected/timestamptz.out | 30 ++++++++++
 src/test/regress/sql/timestamp.sql        | 58 ++++++++++++++++++++
 src/test/regress/sql/timestamptz.sql      | 20 +++++++
 7 files changed, 306 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 02a37658ad..ff3fae9acd 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8715,6 +8715,20 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         </para></entry>
        </row>
 
+       <row>
+        <entry role="func_table_entry"><para role="func_signature">
+         <function>date_bin</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> )
+         <returnvalue>timestamp</returnvalue>
+        </para>
+        <para>
+         Bin input into specified interval aligned with specified origin; see <xref linkend="functions-datetime-trunc"/>
+        </para>
+        <para>
+         <literal>date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal>
+         <returnvalue>2001-02-16 20:35:00</returnvalue>
+        </para></entry>
+       </row>
+
        <row>
         <entry role="func_table_entry"><para role="func_signature">
          <indexterm>
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 0b1f95a5b4..19458b1859 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3812,6 +3812,47 @@ timestamptz_age(PG_FUNCTION_ARGS)
  *---------------------------------------------------------*/
 
 
+/* timestamp_bin()
+ * Bin timestamp into specified interval.
+ */
+Datum
+timestamp_bin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
+	Timestamp	origin = PG_GETARG_TIMESTAMP(2);
+	Timestamp	result,
+				tm_diff,
+				stride_usecs,
+				tm_delta;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("origin out of range")));
+
+	if (stride->month != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("timestamps cannot be binned into intervals containing months or years")));
+
+	stride_usecs = stride->day * USECS_PER_DAY + stride->time;
+
+	/* trivial case of 1 usec */
+	if (stride_usecs == 1)
+		PG_RETURN_TIMESTAMP(timestamp);
+
+	tm_diff = timestamp - origin;
+	tm_delta = tm_diff - tm_diff % stride_usecs;;
+
+	result = origin + tm_delta;
+
+	PG_RETURN_TIMESTAMP(result);
+}
+
 /* timestamp_trunc()
  * Truncate timestamp to specified units.
  */
@@ -3946,6 +3987,47 @@ timestamp_trunc(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMP(result);
 }
 
+/* timestamptz_bin()
+ * Bin timestamptz into specified interval using specified origin.
+ */
+Datum
+timestamptz_bin(PG_FUNCTION_ARGS)
+{
+	Interval   *stride = PG_GETARG_INTERVAL_P(0);
+	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+	TimestampTz	origin = PG_GETARG_TIMESTAMPTZ(2);
+	TimestampTz	result,
+				stride_usecs,
+				tm_diff,
+				tm_delta;
+
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+		return timestamp;
+
+	if (TIMESTAMP_NOT_FINITE(origin))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("origin out of range")));
+
+	if (stride->month != 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("timestamps cannot be binned into intervals containing months or years")));
+
+	stride_usecs = stride->day * USECS_PER_DAY + stride->time;
+
+	/* trivial case of 1 usec */
+	if (stride_usecs == 1)
+		return timestamp;
+
+	tm_diff = timestamp - origin;
+	tm_delta = tm_diff - tm_diff % stride_usecs;;
+
+	result = origin + tm_delta;
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
 /*
  * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d7b55f57ea..522a56e0a4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5755,6 +5755,17 @@
 { oid => '2020', descr => 'truncate timestamp to specified units',
   proname => 'date_trunc', prorettype => 'timestamp',
   proargtypes => 'text timestamp', prosrc => 'timestamp_trunc' },
+
+{ oid => '8990',
+  descr => 'bin timestamp into specified interval',
+  proname => 'date_bin', prorettype => 'timestamp',
+  proargtypes => 'interval timestamp timestamp',
+  prosrc => 'timestamp_bin' },
+{ oid => '8993',
+  descr => 'bin timestamp with time zone into specified interval',
+  proname => 'date_bin', prorettype => 'timestamptz',
+  proargtypes => 'interval timestamptz timestamptz', prosrc => 'timestamptz_bin' },
+
 { oid => '2021', descr => 'extract field from timestamp',
   proname => 'date_part', prorettype => 'float8',
   proargtypes => 'text timestamp', prosrc => 'timestamp_part' },
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index ebaf286201..2efec548e6 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -545,6 +545,97 @@ SELECT date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc
  Mon Feb 23 00:00:00 2004
 (1 row)
 
+-- verify date_bin behaves the same as date_trunc for relevant intervals
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2001-01-01') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2020-02-29 15:44:17.71393') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(7 rows)
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-10 15:44:17.71393 BC') ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(7 rows)
+
+-- bin timestamps into arbitrary intervals
+SELECT
+  interval,
+  date_bin(interval::interval, ts, timestamp '2001-01-01')
+FROM (
+  VALUES
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT timestamp '2020-02-11 15:44:17.71393') ts (ts);
+     interval     |            date_bin            
+------------------+--------------------------------
+ 15 days          | Thu Feb 06 00:00:00 2020
+ 2 hours          | Tue Feb 11 14:00:00 2020
+ 15 minutes       | Tue Feb 11 15:30:00 2020
+ 10 seconds       | Tue Feb 11 15:44:10 2020
+ 100 milliseconds | Tue Feb 11 15:44:17.7 2020
+ 250 microseconds | Tue Feb 11 15:44:17.71375 2020
+(6 rows)
+
+-- shift bins using the origin parameter:
+SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');
+         date_bin         
+--------------------------
+ Sat Feb 01 00:57:30 2020
+(1 row)
+
+-- disallow intervals with months or years
+SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
+ERROR:  timestamps cannot be binned into intervals containing months or years
+SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
+ERROR:  timestamps cannot be binned into intervals containing months or years
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 47f658511d..44f77907df 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -663,6 +663,36 @@ 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)
 
+-- 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
+FROM (
+  VALUES
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT 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
+(6 rows)
+
+-- disallow intervals with months or years
+SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
+ERROR:  timestamps cannot be binned into intervals containing months or years
+SELECT date_bin('5 years'::interval,  timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
+ERROR:  timestamps cannot be binned into intervals containing months or years
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 07f984389d..08bff3613b 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -166,6 +166,64 @@ SELECT d1 - timestamp without time zone '1997-01-02' AS diff
 
 SELECT date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
 
+-- verify date_bin behaves the same as date_trunc for relevant intervals
+
+-- case 1: AD dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2001-01-01') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '2020-02-29 15:44:17.71393') ts (ts);
+
+-- case 2: BC dates, origin < input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2000-01-01 BC') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamp '0055-6-10 15:44:17.71393 BC') ts (ts);
+
+-- bin timestamps into arbitrary intervals
+SELECT
+  interval,
+  date_bin(interval::interval, ts, timestamp '2001-01-01')
+FROM (
+  VALUES
+  ('15 days'),
+  ('2 hours'),
+  ('15 minutes'),
+  ('10 seconds'),
+  ('100 milliseconds'),
+  ('250 microseconds')
+) intervals (interval),
+(SELECT timestamp '2020-02-11 15:44:17.71393') ts (ts);
+
+-- shift bins using the origin parameter:
+SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');
+
+-- disallow intervals with months or years
+SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
+SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
+
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 2231495e21..fd982c3e25 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -193,6 +193,26 @@ SELECT date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Aus
 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
 
+-- 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
+FROM (
+  VALUES
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(SELECT timestamptz '2020-02-29 15:44:17.71393+00') ts (ts);
+
+-- disallow intervals with months or years
+SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
+SELECT date_bin('5 years'::interval,  timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
+
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL

--- ./doc/src/sgml/func.sgml.orig	2021-03-24 18:16:01.269515354 +0100
+++ ./doc/src/sgml/func.sgml	2021-03-24 18:18:31.695819520 +0100
@@ -9907,13 +9907,13 @@
    </para>
 
    <para>
-    In cases full units (1 minute, 1 hour, etc.), it gives the same result as
+    In case of full units (1 minute, 1 hour, etc.), it gives the same result as
     the analogous <function>date_trunc</function> call, but the difference is
     that <function>date_bin</function> can truncate to an arbitrary interval.
    </para>
 
    <para>
-    The <parameter>stride</parameter> interval cannot contain units of month
+    The <parameter>stride</parameter> interval cannot contain units of a month
     or larger.
    </para>
   </sect2>

 src/backend/utils/adt/timestamp.c       | 14 +++++++++
 src/test/regress/expected/timestamp.out | 54 +++++++++++++++++++++++++++++++++
 src/test/regress/sql/timestamp.sql      | 34 +++++++++++++++++++++
 3 files changed, 102 insertions(+)

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index f619b56d6f..0fc468404f 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3844,6 +3844,13 @@ timestamp_bin(PG_FUNCTION_ARGS)
 	tm_diff = timestamp - origin;
 	tm_delta = tm_diff - tm_diff % stride_usecs;
 
+	/*
+	 * Make sure the returned timestamp is at the start of the bin,
+	 * even if the origin is in the future.
+	 */
+	if (origin > timestamp && stride_usecs > 1)
+		tm_delta -= stride_usecs;
+
 	result = origin + tm_delta;
 
 	PG_RETURN_TIMESTAMP(result);
@@ -4015,6 +4022,13 @@ timestamptz_bin(PG_FUNCTION_ARGS)
 	tm_diff = timestamp - origin;
 	tm_delta = tm_diff - tm_diff % stride_usecs;
 
+	/*
+	 * Make sure the returned timestamp is at the start of the bin,
+	 * even if the origin is in the future.
+	 */
+	if (origin > timestamp && stride_usecs > 1)
+		tm_delta -= stride_usecs;
+
 	result = origin + tm_delta;
 
 	PG_RETURN_TIMESTAMPTZ(result);
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 89a856bac9..2550e7d136 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -600,6 +600,60 @@ FROM (
  microsecond | 1 us     | t
 (7 rows)
 
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(7 rows)
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
+     str     | interval | equal 
+-------------+----------+-------
+ week        | 7 d      | t
+ day         | 1 d      | t
+ hour        | 1 h      | t
+ minute      | 1 m      | t
+ second      | 1 s      | t
+ millisecond | 1 ms     | t
+ microsecond | 1 us     | t
+(7 rows)
+
 -- bin timestamps into arbitrary intervals
 SELECT
   interval,
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 256b96163d..2fd954c20b 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -202,6 +202,40 @@ FROM (
 ) intervals (str, interval),
 (VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
 
+-- case 3: AD dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts);
+
+-- case 4: BC dates, origin > input
+SELECT
+  str,
+  interval,
+  date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal
+FROM (
+  VALUES
+  ('week', '7 d'),
+  ('day', '1 d'),
+  ('hour', '1 h'),
+  ('minute', '1 m'),
+  ('second', '1 s'),
+  ('millisecond', '1 ms'),
+  ('microsecond', '1 us')
+) intervals (str, interval),
+(VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts);
+
 -- bin timestamps into arbitrary intervals
 SELECT
   interval,

 doc/src/sgml/func.sgml | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 19285ae136..beb44d365d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9895,6 +9895,22 @@ SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
     aligned with a specified origin.
    </para>
 
+   <para>
+<synopsis>
+date_trunc(<replaceable>stride</replaceable>, <replaceable>source</replaceable>, <replaceable>origin</replaceable>)
+</synopsis>
+    <replaceable>source</replaceable> is a value expression of type
+    <type>timestamp</type> or <type>timestamp with time zone</type>.
+    (Values of type <type>date</type> are cast automatically to
+    <type>timestamp</type>.)
+    <replaceable>stride</replaceable> is a value expression of type <type>
+    interval</type>. Negative intervals are not treated differently from
+    positive intervals.
+    The return value is likewise of type <type>timestamp</type> or
+    <type>timestamp with time zone</type>, and it marks the beginning
+    of the bin into which the <replaceable>source</replaceable> is placed.
+   </para>
+
    <para>
     Examples:
 <screen>

From 09a4795eb589f08305aa2597059248ad11447ef0 Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Tue, 27 Jul 2021 11:58:16 -0400
Subject: [PATCH] Disallow negative strides in date_bin()

It's not clear what the semantics of negative strides would be, so throw
an error instead.

Per report from Bauyrzhan Sakhariyev

Backpatch to v14

Discussion: https://www.postgresql.org/message-id/CAKpL73vZmLuFVuwF26FJ%2BNk11PVHhAnQRoREFcA03x7znRoFvA%40mail.gmail.com
---
 src/backend/utils/adt/timestamp.c         | 8 ++++----
 src/test/regress/expected/timestamp.out   | 5 ++++-
 src/test/regress/expected/timestamptz.out | 5 ++++-
 src/test/regress/sql/timestamp.sql        | 3 +++
 src/test/regress/sql/timestamptz.sql      | 3 +++
 5 files changed, 18 insertions(+), 6 deletions(-)

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ea847576cd..1c0bf0aa5c 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3843,10 +3843,10 @@ timestamp_bin(PG_FUNCTION_ARGS)
 
 	stride_usecs = stride->day * USECS_PER_DAY + stride->time;
 
-	if (stride_usecs == 0)
+	if (stride_usecs <= 0)
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
-				 errmsg("stride cannot equal zero")));
+				 errmsg("stride must be greater than zero")));
 
 	tm_diff = timestamp - origin;
 	tm_delta = tm_diff - tm_diff % stride_usecs;
@@ -4026,10 +4026,10 @@ timestamptz_bin(PG_FUNCTION_ARGS)
 
 	stride_usecs = stride->day * USECS_PER_DAY + stride->time;
 
-	if (stride_usecs == 0)
+	if (stride_usecs <= 0)
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
-				 errmsg("stride cannot equal zero")));
+				 errmsg("stride must be greater than zero")));
 
 	tm_diff = timestamp - origin;
 	tm_delta = tm_diff - tm_diff % stride_usecs;
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 7a2cbd9b3f..1a2d48cae9 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -706,7 +706,10 @@ SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp
 ERROR:  timestamps cannot be binned into intervals containing months or years
 -- disallow zero intervals
 SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
-ERROR:  stride cannot equal zero
+ERROR:  stride must be greater than zero
+-- disallow negative intervals
+SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
+ERROR:  stride must be greater than zero
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index be6ead0fb5..990c4eddf1 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -750,7 +750,10 @@ SELECT date_bin('5 years'::interval,  timestamp with time zone '2020-02-01 01:01
 ERROR:  timestamps cannot be binned into intervals containing months or years
 -- disallow zero intervals
 SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
-ERROR:  stride cannot equal zero
+ERROR:  stride must be greater than zero
+-- disallow negative intervals
+SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
+ERROR:  stride must be greater than zero
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7307a24092..e011e779ea 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -266,6 +266,9 @@ SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp
 -- disallow zero intervals
 SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
 
+-- disallow negative intervals
+SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
+
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 3642d8c143..b18821de53 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -241,6 +241,9 @@ SELECT date_bin('5 years'::interval,  timestamp with time zone '2020-02-01 01:01
 -- disallow zero intervals
 SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
 
+-- disallow negative intervals
+SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
+
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL
-- 
2.31.1