Thread: date_trunc() in a specific time zone
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
Attachment
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
Andreas Karlsson <andreas@proxel.se> 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
On 29/10/2018 16:36, Tom Lane wrote: > Andreas Karlsson <andreas@proxel.se> 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
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
On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing <vik.fearing@2ndquadrant.com> 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
On 29/10/2018 17:12, Steve Crawford wrote: > On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing <vik.fearing@2ndquadrant.com > <mailto:vik.fearing@2ndquadrant.com>> 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
On Mon, Oct 29, 2018 at 9:40 AM Vik Fearing <vik.fearing@2ndquadrant.com> 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
Paul A Jungwirth <pj@illuminatedcomputing.com> 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
On Mon, Oct 29, 2018 at 9:53 AM Tom Lane <tgl@sss.pgh.pa.us> 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
On Mon, Oct 29, 2018 at 9:58 AM Paul A Jungwirth <pj@illuminatedcomputing.com> 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
On Mon, Oct 29, 2018 at 10:13 AM Paul A Jungwirth <pj@illuminatedcomputing.com> 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
Paul A Jungwirth <pj@illuminatedcomputing.com> 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
>> 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.
On 10/29/2018 04:36 PM, Tom Lane wrote: > Andreas Karlsson <andreas@proxel.se> 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
>>>>> "Paul" == Paul A Jungwirth <pj@illuminatedcomputing.com> 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)
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
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
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
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