date_trunc() in a specific time zone

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

date_trunc() in a specific time zone

Vik Fearing-4
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

date_trunc_tz__001.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Andreas Karlsson
On 10/29/2018 04:18 PM, Vik Fearing wrote:
> 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.

Hm, I am not sure if I see any major win from writing

date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')

instead of

date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE
'Australia/Sydney')

. Especially since you still will have to do the second for other time
related functions like date(). Maybe a slight win in that new users who
read the manual will be reminded that they need to care about time
zones, but I also see a value in teaching users about how to use "AT
TIME ZONE".

Andreas

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Tom Lane-2
Andreas Karlsson <[hidden email]> writes:
> Hm, I am not sure if I see any major win from writing
> date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
> instead of
> date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE
> 'Australia/Sydney')

The latter would give you timestamp without time zone, whereas I think
what Vik wants is timestamp with time zone.  Yeah, you could then convert
it back with a second application of AT TIME ZONE 'Australia/Sydney',
but that's both inefficient and mighty confusing.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Vik Fearing-4
On 29/10/2018 16:36, Tom Lane wrote:

> Andreas Karlsson <[hidden email]> writes:
>> Hm, I am not sure if I see any major win from writing
>> date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
>> instead of
>> date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE
>> 'Australia/Sydney')
>
> The latter would give you timestamp without time zone, whereas I think
> what Vik wants is timestamp with time zone.  Yeah, you could then convert
> it back with a second application of AT TIME ZONE 'Australia/Sydney',
> but that's both inefficient

Hmm, converting it back is exactly what my function does...

> and mighty confusing.

This is my justification for needing the patch.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Vik Fearing-4
In reply to this post by Andreas Karlsson
On 29/10/2018 16:26, Andreas Karlsson wrote:

> On 10/29/2018 04:18 PM, Vik Fearing wrote:
>> 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.
>
> Hm, I am not sure if I see any major win from writing
>
> date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
>
> instead of
>
> date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE
> 'Australia/Sydney')

Because I don't want '2001-02-16 00:00:00' (where?), I want the precise
moment in time that that represents ('2001-02-16 13:00:00+00') so I can
pull the correct rows out of my big table.

This isn't for display purposes.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Steve Crawford
On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing <[hidden email]> wrote:
On 29/10/2018 16:26, Andreas Karlsson wrote:
> On 10/29/2018 04:18 PM, Vik Fearing wrote:
>> 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.
>
> Hm, I am not sure if I see any major win from writing
>
> date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
>
> instead of
>
> date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE
> 'Australia/Sydney')

Because I don't want '2001-02-16 00:00:00' (where?), I want the precise
moment in time that that represents ('2001-02-16 13:00:00+00') so I can
pull the correct rows out of my big table.

This isn't for display purposes.


I'm a bit confused as to the use case. Wouldn't someone who wants locally-based time-period ranges also want output displayed in the corresponding zone both of which are already well handled in one place by "set timezone..."?

Cheers,
Steve

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Vik Fearing-4
On 29/10/2018 17:12, Steve Crawford wrote:

> On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     On 29/10/2018 16:26, Andreas Karlsson wrote:
>     > On 10/29/2018 04:18 PM, Vik Fearing wrote:
>     >> 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.
>     >
>     > Hm, I am not sure if I see any major win from writing
>     >
>     > date_trunc('day', timestamptz '2001-02-16 20:38:40+00',
>     'Australia/Sydney')
>     >
>     > instead of
>     >
>     > date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE
>     > 'Australia/Sydney')
>
>     Because I don't want '2001-02-16 00:00:00' (where?), I want the precise
>     moment in time that that represents ('2001-02-16 13:00:00+00') so I can
>     pull the correct rows out of my big table.
>
>     This isn't for display purposes.
>
>
> I'm a bit confused as to the use case. Wouldn't someone who wants
> locally-based time-period ranges also want output displayed in the
> corresponding zone both of which are already well handled in one place
> by "set timezone..."?

You're confusing projection and selection.  I don't necessarily want to
display any dates, I could be grouping and aggregating.  Setting the
session timezone is also not adequate because I could be handling data
from different time zones.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Paul A Jungwirth
On Mon, Oct 29, 2018 at 9:40 AM Vik Fearing <[hidden email]> wrote:
> I don't necessarily want to
> display any dates, I could be grouping and aggregating.

I can attest that this patch would be really nice functionality to
have. Grouping is an often-overlooked case where timezones matter.
When grouping by day (e.g.), the timezone determines where you "slice
up" the timeline. I've needed this often enough that I have a personal
extension for it (https://github.com/pjungwir/pjpg) but that repo is a
bit sloppy, and I can't use it on managed services like RDS.

It would be nice to support both timestamptz and timestamp, with the
output matching whatever type you give as input. I know which to use
is controversial, but plain timestamp is the default in Ruby on Rails,
so people encounter it a lot.

Yours,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Tom Lane-2
Paul A Jungwirth <[hidden email]> writes:
> It would be nice to support both timestamptz and timestamp, with the
> output matching whatever type you give as input. I know which to use
> is controversial, but plain timestamp is the default in Ruby on Rails,
> so people encounter it a lot.

We already have date_trunc() for timestamp-without-tz, so I'm a little
confused as to what you think is/would be missing.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Paul A Jungwirth
On Mon, Oct 29, 2018 at 9:53 AM Tom Lane <[hidden email]> wrote:
> We already have date_trunc() for timestamp-without-tz, so I'm a little
> confused as to what you think is/would be missing.

This patch adds a 3-arg version for date_trunc(text, timestamptz,
text). I'm saying it would be nice to also have a 3-arg version for
date_trunc(text, timestamp, text). It would do the same thing, except
take a timestamp w/o tz and return a timestamp w/o tz.

Paul

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Paul A Jungwirth
On Mon, Oct 29, 2018 at 9:58 AM Paul A Jungwirth
<[hidden email]> wrote:
> This patch adds a 3-arg version for date_trunc(text, timestamptz,
> text). I'm saying it would be nice to also have a 3-arg version for
> date_trunc(text, timestamp, text). It would do the same thing, except
> take a timestamp w/o tz and return a timestamp w/o tz.

Thinking about this some more, perhaps the timestamp-to-timestamp
version would depend on assumptions that aren't always valid. In my
world the server timezone is always UTC, and the database clients
always convert values to UTC before saving. But if those assumptions
don't hold the simple implementation of 2x AT TIME ZONE might not work
correctly. I can try it out and see....

Paul

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Paul A Jungwirth
On Mon, Oct 29, 2018 at 10:13 AM Paul A Jungwirth
<[hidden email]> wrote:
> But if those assumptions
> don't hold the simple implementation of 2x AT TIME ZONE might not work
> correctly. I can try it out and see....

Okay it looks to me that my suggestion won't work for the general
case. Basically I'm looking for this:

    date_trunc($1, $2 at time zone $3) at time zone 'UTC'

not:

    date_trunc($1, $2 at time zone $3) at time zone $3

Using $3 in both places is correct for tstz-to-tstz, but not for
ts-to-ts. For example, given a table where t1 is timestamptz and t2 is
timestamp:

paul=# select * from times;
           t1           |         t2
------------------------+---------------------
 2018-10-29 10:18:00-07 | 2018-10-29 10:18:00
 2018-10-29 18:18:00-07 | 2018-10-29 18:18:00
(2 rows)

This is wrong:

paul=# select date_trunc('day', t2 at time zone 'America/Los_Angeles')
at time zone 'America/Los_Angeles' from times;
      timezone
---------------------
 2018-10-29 00:00:00
 2018-10-29 00:00:00
(2 rows)

But this is what I'd want:

paul=# select date_trunc('day', t2 at time zone 'America/Los_Angeles')
at time zone 'UTC' from times;
      timezone
---------------------
 2018-10-29 07:00:00
 2018-10-29 07:00:00
(2 rows)

I guess the issue is that for w/o-tz, you need an extra parameter to
say what you're assuming you started with. Sorry for the distraction.
Anyway, I think Vik's patch is great and I would use it! :-)

Paul

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Tom Lane-2
Paul A Jungwirth <[hidden email]> writes:
> I guess the issue is that for w/o-tz, you need an extra parameter to
> say what you're assuming you started with.

Yeah, that's basically what I was wondering.  I suppose we could imagine
a 4-argument function to cover that case, but I do not think it's worth
the trouble, given that there are other ways to do it.

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.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Corey Huinker
In reply to this post by Vik Fearing-4
>> 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.


+1 A client encountered this exact problem last week, and I was surprised that the parameter didn't already exist. 
Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Andreas Karlsson
In reply to this post by Tom Lane-2
On 10/29/2018 04:36 PM, Tom Lane wrote:

> Andreas Karlsson <[hidden email]> writes:
>> Hm, I am not sure if I see any major win from writing
>> date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
>> instead of
>> date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE
>> 'Australia/Sydney')
>
> The latter would give you timestamp without time zone, whereas I think
> what Vik wants is timestamp with time zone.  Yeah, you could then convert
> it back with a second application of AT TIME ZONE 'Australia/Sydney',
> but that's both inefficient and mighty confusing.

Sloppy reading on my part, thanks for pointing it out.

Andreas

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Andrew Gierth
In reply to this post by Paul A Jungwirth
>>>>> "Paul" == Paul A Jungwirth <[hidden email]> writes:

 Paul> Thinking about this some more, perhaps the timestamp-to-timestamp
 Paul> version would depend on assumptions that aren't always valid. In
 Paul> my world the server timezone is always UTC, and the database
 Paul> clients always convert values to UTC before saving. But if those
 Paul> assumptions don't hold the simple implementation of 2x AT TIME
 Paul> ZONE might not work correctly. I can try it out and see....

There's a reason we tell people not to do this (i.e. not to store UTC
values in timestamp w/o tz columns) and to use timestamptz instead.
I should probably add an even more explicit entry to expand on
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

If you have a timestamp-in-UTC column and want to do a date_trunc in
some other specified zone (that's not the session timezone), you need
FOUR uses of AT TIME ZONE to do it correctly:

date_trunc('day', col AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kathmandu')
  AT TIME ZONE 'Asia/Kathmandu' AT TIME ZONE 'UTC'

You can replace two of them with casts, but only at the cost of assuming
that the session timezone really is set to UTC, so there's no real gain.

With a date_trunc(text, timestamptz, text) function, the above could be
simplified to:

date_trunc('day', col AT TIME ZONE 'UTC', 'Asia/Kathmandu')
  AT TIME ZONE 'UTC'

--
Andrew (irc:RhodiumToad)

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

David Fetter
In reply to this post by Vik Fearing-4
On Mon, Oct 29, 2018 at 04:18:23PM +0100, Vik Fearing wrote:
> 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.

+1

In a slightly related matter, at some point, we also need to come up
with a timestamptz2 or some such which preserves the input time zone.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Tom Lane-2
In reply to this post by Tom Lane-2
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
Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Tom Lane-2
I wrote:
> Here's a v2 that transposes the code to C so that we can get that
> optimization.

Pushed after a bit more testing and documentation-wordsmithing.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: date_trunc() in a specific time zone

Vik Fearing-4
On 14/11/2018 21:42, Tom Lane wrote:
> I wrote:
>> Here's a v2 that transposes the code to C so that we can get that
>> optimization.
>
> Pushed after a bit more testing and documentation-wordsmithing.

Thank you, Tom!
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support