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 <vik(dot)fearing(at)2ndquadrant(dot)com
> <mailto:vik(dot)fearing(at)2ndquadrant(dot)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