Horton

Thread

Commits

  1. Add a timezone-specific variant of date_trunc().

  1. date_trunc() in a specific time zone

    Vik Fearing <vik.fearing@2ndquadrant.com> — 2018-10-29T15:18:23Z

    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

  2. Re: date_trunc() in a specific time zone

    Andreas Karlsson <andreas@proxel.se> — 2018-10-29T15:26:31Z

    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

  3. Re: date_trunc() in a specific time zone

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-10-29T15:36:32Z

    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

  4. Re: date_trunc() in a specific time zone

    Vik Fearing <vik.fearing@2ndquadrant.com> — 2018-10-29T15:40:24Z

    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

  5. Re: date_trunc() in a specific time zone

    Vik Fearing <vik.fearing@2ndquadrant.com> — 2018-10-29T15:43:40Z

    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

  6. Re: date_trunc() in a specific time zone

    Steve Crawford <scrawford@pinpointresearch.com> — 2018-10-29T16:12:49Z

    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

  7. Re: date_trunc() in a specific time zone

    Vik Fearing <vik.fearing@2ndquadrant.com> — 2018-10-29T16:40:26Z

    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

  8. Re: date_trunc() in a specific time zone

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2018-10-29T16:50:07Z

    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

  9. Re: date_trunc() in a specific time zone

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-10-29T16:53:16Z

    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

  10. Re: date_trunc() in a specific time zone

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2018-10-29T16:58:58Z

    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

  11. Re: date_trunc() in a specific time zone

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2018-10-29T17:13:10Z

    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

  12. Re: date_trunc() in a specific time zone

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2018-10-29T17:29:37Z

    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

  13. Re: date_trunc() in a specific time zone

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-10-29T18:46:15Z

    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

  14. Re: date_trunc() in a specific time zone

    Corey Huinker <corey.huinker@gmail.com> — 2018-10-29T23:04:10Z

    >
> >> 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.

  15. Re: date_trunc() in a specific time zone

    Andreas Karlsson <andreas@proxel.se> — 2018-10-29T23:18:41Z

    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

  16. Re: date_trunc() in a specific time zone

    Andrew Gierth <andrew@tao11.riddles.org.uk> — 2018-10-29T23:25:47Z

    >>>>> "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)

  17. Re: date_trunc() in a specific time zone

    David Fetter <david@fetter.org> — 2018-11-04T20:35:22Z

    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

  18. Re: date_trunc() in a specific time zone

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-11-13T23:20:30Z

    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

  19. Re: date_trunc() in a specific time zone

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-11-14T20:42:31Z

    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

  20. Re: date_trunc() in a specific time zone

    Vik Fearing <vik.fearing@2ndquadrant.com> — 2018-11-14T21:42:04Z

    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