Re: [BUGS] BUG #14660: Conversion to UTC not properly computed

tbrandys@webcellence.com writes:
> vendita=# select now()::timestamp(0), now()::timestamp(0) with time zone,
> timezone('utc', now()::timestamp(0)), timezone('utc', now()::timestamp(0)
> with time zone);
>          now         |          now           |        timezone        |
>  timezone
> ---------------------+------------------------+------------------------+---------------------
>  2017-05-18 08:55:10 | 2017-05-18 08:55:10-07 | 2017-05-18 01:55:10-07 |
> 2017-05-18 15:55:10
> (1 row)

> vendita=# show timezone;
>   TimeZone
> ------------
>  US/Arizona
> (1 row)

> The computation where the assumed use of the local time zone should occur is
> incorrect (the third column).

Looks fine to me.  As per your first column, the result of now()::timestamp(0)
is '2017-05-18 08:55:10' (with no zone implied).  timezone('utc', ...)
converts that into a timestamptz assuming that it's UTC, that is the value
is effectively '2017-05-18 08:55:10+00'.  However, the timestamptz output
function rotates that back to the current TimeZone zone for display
purposes, so you see '2017-05-18 01:55:10-07' --- which is the same time
instant, just in a different zone.

I think perhaps what's confusing you is that PG's timestamptz values don't
store a zone value but just a time instant, which is why timestamptz_out
has to assume a zone to display in.  That's not really per SQL spec, but
changing it would be very difficult, so I'm not holding my breath waiting
for it to happen.
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs