Re: date_trunc() in a specific time zone

On Mon, Oct 29, 2018 at 10:13 AM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)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