A use case that I see quite a lot of is needing to do reports and other
calculations on data per day/hour/etc but in the user's time zone. The
way to do that is fairly trivial, but it's not obvious what it does so
reading queries becomes just a little bit more difficult.
Attached is a patch to create a function for it, based off 5953c99697.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 96d45419e5..304fa638fe 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -7185,6 +7185,15 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<entry><literal>2001-02-16 20:00:00</literal></entry>
</row>
+ <row>
+ <entry><literal><function>date_trunc(<type>text</type>, <type>timestamp with time zone</type>, <type>text</type>)</function></literal></entry>
+ <entry><type>timestamp with time zone</type></entry>
+ <entry>Truncate to specified precision at the specified time zone; see also <xref linkend="functions-datetime-trunc"/>
+ </entry>
+ <entry><literal>date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</literal></entry>
+ <entry><literal>2001-02-16 13:00:00+00</literal></entry>
+ </row>
+
<row>
<entry><literal><function>date_trunc(<type>text</type>, <type>interval</type>)</function></literal></entry>
<entry><type>interval</type></entry>
@@ -8078,7 +8087,7 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
<para>
<synopsis>
-date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
+date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable> [, <replaceable>time_zone</replaceable>])
</synopsis>
<replaceable>source</replaceable> is a value expression of type
<type>timestamp</type> or <type>interval</type>.
@@ -8090,6 +8099,9 @@ date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>
<type>timestamp</type> or <type>interval</type>
with all fields that are less significant than the
selected one set to zero (or one, for day and month).
+ If the optional <replaceable>time_zone</replaceable> is present,
+ the <replaceable>source</replaceable> value is truncated in the
+ specified time zone.
</para>
<para>
@@ -8119,6 +8131,9 @@ SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
+
+SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 13:00:00+00</computeroutput>
</screen>
</para>
</sect2>
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4d7fe1b383..9b20752cde 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2280,6 +2280,10 @@
descr => 'truncate timestamp with time zone to specified units',
proname => 'date_trunc', provolatile => 's', prorettype => 'timestamptz',
proargtypes => 'text timestamptz', prosrc => 'timestamptz_trunc' },
+{ oid => '1284',
+ descr => 'truncate timestamp with time zone to specified units at specified time zone',
+ proname => 'date_trunc', prolang => '14', provolatile => 's', prorettype => 'timestamptz',
+ proargtypes => 'text timestamptz text', prosrc => 'select pg_catalog.date_trunc($1, $2 at time zone $3) at time zone $3' },
{ oid => '1218', descr => 'truncate interval to specified units',
proname => 'date_trunc', prorettype => 'interval',
proargtypes => 'text interval', prosrc => 'interval_trunc' },
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 2340f30794..bac5c36591 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -649,6 +649,12 @@ SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2004
| Mon Feb 23 00:00:00 2004 PST
(1 row)
+SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc;
+ date_trunc_at_tz | sydney_trunc
+------------------+------------------------------
+ | Fri Feb 16 05:00:00 2001 PST
+(1 row)
+
-- 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/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index f17d153fcc..b7eff45a5b 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -192,6 +192,7 @@ SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc;
+SELECT '' AS date_trunc_at_tz, date_trunc('day', timestamp with time zone '2001-02-16 20:38:40+00', 'Australia/Sydney') as sydney_trunc;
-- Test casting within a BETWEEN qualifier
SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
