I wrote:
> BTW, I'd been hoping that we could avoid rotate-to-local-and-back
> in Vik's desired case, but after further thought I suspect the only
> real optimization that's possible compared to writing it out with
> two AT TIME ZONE constructs is to do the zone name lookup just once.
> As an example, truncating to a day-or-larger boundary could result in
> shifting to a different UTC offset than you started with, due to crossing
> a DST boundary.
Here's a v2 that transposes the code to C so that we can get that
optimization. I've not tried to time it, but it should actually be
a bit faster than standard date_trunc plus one AT TIME ZONE rotation,
never mind two of them.
regards, tom lane
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1678c8c..adffa7d 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT regexp_match('abc01234xyz', '(?:(
*** 7186,7191 ****
--- 7186,7200 ----
</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 in 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>
<entry>Truncate to specified precision; see also <xref linkend="functions-datetime-trunc"/>
*************** SELECT date_part('hour', INTERVAL '4 hou
*** 8078,8084 ****
<para>
<synopsis>
! date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
</synopsis>
<replaceable>source</replaceable> is a value expression of type
<type>timestamp</type> or <type>interval</type>.
--- 8087,8093 ----
<para>
<synopsis>
! 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>.
*************** date_trunc('<replaceable>field</replacea
*** 8112,8124 ****
</para>
<para>
! Examples:
<screen>
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
</screen>
</para>
</sect2>
--- 8121,8158 ----
</para>
<para>
! If the optional <replaceable>time_zone</replaceable> argument is
! present, the <replaceable>source</replaceable> value is truncated in the
! specified time zone; for example, truncation to <literal>day</literal>
! produces a value that is midnight in that zone. The time zone name can
! be specified in any of the ways described in
! <xref linkend="datatype-timezones"/>.
! </para>
!
! <para>
! When the <replaceable>time_zone</replaceable> argument is
! present, the <replaceable>source</replaceable> and result are always of
! type <type>timestamp with time zone</type>, whereas the two-argument
! form of <function>date_trunc</function> is available for timestamps with
! or without time zone. The two-argument form truncates <type>timestamp
! with time zone</type> values using the current
! <xref linkend="guc-timezone"/> setting.
! </para>
!
! <para>
! Examples (assuming the local time zone is <literal>America/New_York</literal>):
<screen>
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
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 00:00:00-05</computeroutput>
+
+ SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
+ <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 08:00:00-05</computeroutput>
</screen>
</para>
</sect2>
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 449164a..e5f8b51 100644
*** a/src/backend/utils/adt/timestamp.c
--- b/src/backend/utils/adt/timestamp.c
*************** timestamp_trunc(PG_FUNCTION_ARGS)
*** 3925,3938 ****
PG_RETURN_TIMESTAMP(result);
}
! /* timestamptz_trunc()
! * Truncate timestamp to specified units.
*/
! Datum
! timestamptz_trunc(PG_FUNCTION_ARGS)
{
- text *units = PG_GETARG_TEXT_PP(0);
- TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
TimestampTz result;
int tz;
int type,
--- 3925,3939 ----
PG_RETURN_TIMESTAMP(result);
}
! /*
! * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
! *
! * tzp identifies the zone to truncate with respect to. We assume
! * infinite timestamps have already been rejected.
*/
! static TimestampTz
! timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
{
TimestampTz result;
int tz;
int type,
*************** timestamptz_trunc(PG_FUNCTION_ARGS)
*** 3943,3951 ****
struct pg_tm tt,
*tm = &tt;
- if (TIMESTAMP_NOT_FINITE(timestamp))
- PG_RETURN_TIMESTAMPTZ(timestamp);
-
lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
VARSIZE_ANY_EXHDR(units),
false);
--- 3944,3949 ----
*************** timestamptz_trunc(PG_FUNCTION_ARGS)
*** 3954,3960 ****
if (type == UNITS)
{
! if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
--- 3952,3958 ----
if (type == UNITS)
{
! if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
*************** timestamptz_trunc(PG_FUNCTION_ARGS)
*** 4055,4061 ****
}
if (redotz)
! tz = DetermineTimeZoneOffset(tm, session_timezone);
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
ereport(ERROR,
--- 4053,4059 ----
}
if (redotz)
! tz = DetermineTimeZoneOffset(tm, tzp);
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
ereport(ERROR,
*************** timestamptz_trunc(PG_FUNCTION_ARGS)
*** 4071,4076 ****
--- 4069,4151 ----
result = 0;
}
+ return result;
+ }
+
+ /* timestamptz_trunc()
+ * Truncate timestamptz to specified units.
+ */
+ Datum
+ timestamptz_trunc(PG_FUNCTION_ARGS)
+ {
+ text *units = PG_GETARG_TEXT_PP(0);
+ TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
+ TimestampTz result;
+
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMPTZ(timestamp);
+
+ result = timestamptz_trunc_internal(units, timestamp, session_timezone);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+ }
+
+ /* timestamptz_trunc_zone()
+ * Truncate timestamptz to specified units in specified timezone.
+ */
+ Datum
+ timestamptz_trunc_zone(PG_FUNCTION_ARGS)
+ {
+ text *units = PG_GETARG_TEXT_PP(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;
+
+ /*
+ * timestamptz_zone() doesn't look up the zone for infinite inputs, so we
+ * don't do so here either.
+ */
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMP(timestamp);
+
+ /*
+ * 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_internal(units, timestamp, tzp);
+
PG_RETURN_TIMESTAMPTZ(result);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4026018..9264a2e 100644
*** a/src/include/catalog/pg_proc.dat
--- b/src/include/catalog/pg_proc.dat
***************
*** 2280,2285 ****
--- 2280,2289 ----
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 in specified time zone',
+ proname => 'date_trunc', provolatile => 's', prorettype => 'timestamptz',
+ proargtypes => 'text timestamptz text', prosrc => 'timestamptz_trunc_zone' },
{ 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 2340f30..8a4c719 100644
*** a/src/test/regress/expected/timestamptz.out
--- b/src/test/regress/expected/timestamptz.out
*************** SELECT '' AS date_trunc_week, date_trunc
*** 649,654 ****
--- 649,672 ----
| 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; -- zone name
+ date_trunc_at_tz | sydney_trunc
+ ------------------+------------------------------
+ | Fri Feb 16 05:00:00 2001 PST
+ (1 row)
+
+ 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
+ date_trunc_at_tz | gmt_trunc
+ ------------------+------------------------------
+ | Thu Feb 15 16:00:00 2001 PST
+ (1 row)
+
+ 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
+ date_trunc_at_tz | vet_trunc
+ ------------------+------------------------------
+ | Thu Feb 15 20: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 f17d153..c3bd46c 100644
*** a/src/test/regress/sql/timestamptz.sql
--- b/src/test/regress/sql/timestamptz.sql
*************** SELECT '' AS "54", d1 - timestamp with t
*** 193,198 ****
--- 193,202 ----
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; -- zone name
+ 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
+
-- Test casting within a BETWEEN qualifier
SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL
