Re: BUG #14660: Conversion to UTC not properly computed

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: tbrandys(at)webcellence(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #14660: Conversion to UTC not properly computed
Date: 2017-05-18 16:24:47
Message-ID: 25205.1495124687@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

tbrandys(at)webcellence(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Vitaliy Gomenyuk 2017-05-18 16:26:29 Re: BUG #14635: Query is executed slower on hot standby slave database then on master database
Previous Message Marko Tiikkaja 2017-05-18 16:24:39 Re: BUG #14660: Conversion to UTC not properly computed