Re: date_trunc() in a specific time zone

2018-11-14 Thread Vik Fearing 
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

Re: date_trunc() in a specific time zone

2018-11-14 Thread Tom Lane 
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

Re: date_trunc() in a specific time zone

2018-11-13 Thread Tom Lane 
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 
 
  
 
+ date_trunc(text, timestamp with time zone, text)
+ timestamp with time zone
+ Truncate to specified precision in the specified time zone; see also 
+ 
+ date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
+ 2001-02-16 13:00:00+00
+
+ 
+
  date_trunc(text, interval)
  interval
  Truncate to specified precision; see also 
*** SELECT date_part('hour', INTERVAL '4 hou
*** 8078,8084 
  
 
  
! date_trunc('field', source)
  
  source is a value expression of type
  timestamp or interval.
--- 8087,8093 
  
 
  
! date_trunc(field, source [, time_zone])
  
  source is a value expression of type
  timestamp or interval.
*** date_trunc('field
  
 
! Examples:
  
  SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
  Result: 2001-02-16 20:00:00
  
  SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
  Result: 2001-01-01 00:00:00
  
 

--- 8121,8158 
 
  
 
! If the optional time_zone argument is
! present, the source value is truncated in the
! specified time zone; for example, truncation to day
! produces a value that is midnight in that zone.  The time zone name can
! be specified in any of the ways described in
! .
!
! 
!
! When the time_zone argument is
! present, the source and result are always of
! type timestamp with time zone, whereas the two-argument
! form of date_trunc is available for timestamps with
! or without time zone.  The two-argument form truncates timestamp
! with time zone values using the current
!  setting.
!
! 
!
! Examples (assuming the local time zone is America/New_York):
  
  SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
  Result: 2001-02-16 20:00:00
  
  SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
  Result: 2001-01-01 00:00:00
+ 
+ SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
+ Result: 2001-02-16 00:00:00-05
+ 
+ SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
+ Result: 2001-02-16 08:00:00-05
  
 

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 = 
  
- 	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, , tm, , 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, , tm, , NULL, tzp) != 0)
  			ereport(ERROR,

Re: date_trunc() in a specific time zone

2018-11-04 Thread David Fetter 
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  http://fetter.org/
Phone: +1 415 235 3778

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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Andrew Gierth 
> "Paul" == Paul A Jungwirth  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)

Re: date_trunc() in a specific time zone

2018-10-29 Thread Andreas Karlsson 

On 10/29/2018 04:36 PM, Tom Lane wrote:

Andreas Karlsson  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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Corey Huinker 
>
> >> 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.

Re: date_trunc() in a specific time zone

2018-10-29 Thread Tom Lane 
Paul A Jungwirth  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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth 
On Mon, Oct 29, 2018 at 10:13 AM Paul A Jungwirth
 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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth 
On Mon, Oct 29, 2018 at 9:58 AM Paul A Jungwirth
 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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth 
On Mon, Oct 29, 2018 at 9:53 AM Tom Lane  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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth 
On Mon, Oct 29, 2018 at 9:40 AM Vik Fearing  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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Vik Fearing 
On 29/10/2018 17:12, Steve Crawford wrote:
> On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing  > 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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Steve Crawford 
On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing 
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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Vik Fearing 
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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Vik Fearing 
On 29/10/2018 16:36, Tom Lane wrote:
> Andreas Karlsson  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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Tom Lane 
Andreas Karlsson  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

Re: date_trunc() in a specific time zone

2018-10-29 Thread 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