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

The following bug has been logged on the website:

Bug reference:      14660
Logged by:          Todd Brandys
Email address:      tbrandys@webcellence.com
PostgreSQL version: 9.6.3
Operating system:   Slackware Linux
Description:

According to
https://www.postgresql.org/docs/9.6/static/datatype-datetime.html

Conversions between timestamp without time zone and timestamp with time zone
normally assume that the timestamp without time zone value should be taken
or given as timezone local time. A different time zone can be specified for
the conversion using AT TIME ZONE.

With this in mind, the following output is troublesome:

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-1808: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).


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

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