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
