Re: date_trunc() in a specific time zone

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

Re: date_trunc() in a specific time zone

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

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 examp

Re: date_trunc() in a specific time zone

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

Re: date_trunc() in a specific time zone

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

Re: date_trunc() in a specific time zone

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

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

Re: date_trunc() in a specific time zone

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

Re: date_trunc() in a specific time zone

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

Re: date_trunc() in a specific time zone

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 re

Re: date_trunc() in a specific time zone

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 v

Re: date_trunc() in a specific time zone

Paul A Jungwirth 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_trun

Re: date_trunc() in a specific time zone

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.

Re: date_trunc() in a specific time zone

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

Re: date_trunc() in a specific time zone

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

Re: date_trunc() in a specific time zone

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 w

Re: date_trunc() in a specific time zone

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 >> 'Australi

Re: date_trunc() in a specific time zone

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 timest

Re: date_trunc() in a specific time zone

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 b

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 cr