Re: date_trunc() in a specific time zone

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