datestyle=postgres broken with timezone=UTC+N

Christoph Berg <cb@df7cb.de> writes:

postgres =# set timezone = 'Etc/UTC+1';
SET
postgres =# set datestyle = 'postgres';
SET
postgres =# select '2015-01-01 01:00:00 +0100'::timestamptz;
Wed 31 Dec 23:00:00 2014 ETC/UTC

postgres =# select 'Wed 31 Dec 23:00:00 2014 ETC/UTC'::timestamptz;
Wed 31 Dec 22:00:00 2014 ETC/UTC

Not sure I see your point? Lacking any entry for ETC/UTC in the
timezone_abbreviations file, we'll look it up in the zoneinfo database,
and that will tell us it means UTC+0. So the input means 2300 UTC, and
that gets displayed as 2200 in your UTC-1 display timezone.

No, this isn't entirely consistent with the way the timezone name is being
used in output. But that's because you've only half-configured the system
for a weird timezone name. You would need an abbreviation entry as well
if you want "ETC/UTC" to be read as UTC-1.

For more info read
http://www.postgresql.org/docs/9.4/static/datatype-datetime.html#DATATYPE-TIMEZONES
--- see particularly the FOOBAR0 example, which is exactly what you've
got here.  By and large, I don't recommend use of POSIX notation in
timezone settings; the IANA geographical zone names are much less likely
to bite you on the rear.

regards, tom lane

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

Re: Tom Lane 2015-08-30 <20976.1440946967@sss.pgh.pa.us>

Christoph Berg <cb@df7cb.de> writes:

postgres =# set timezone = 'Etc/UTC+1';
SET
postgres =# set datestyle = 'postgres';
SET
postgres =# select '2015-01-01 01:00:00 +0100'::timestamptz;
Wed 31 Dec 23:00:00 2014 ETC/UTC

postgres =# select 'Wed 31 Dec 23:00:00 2014 ETC/UTC'::timestamptz;
Wed 31 Dec 22:00:00 2014 ETC/UTC

Not sure I see your point? Lacking any entry for ETC/UTC in the
timezone_abbreviations file, we'll look it up in the zoneinfo database,
and that will tell us it means UTC+0. So the input means 2300 UTC, and
that gets displayed as 2200 in your UTC-1 display timezone.

No, this isn't entirely consistent with the way the timezone name is being
used in output. But that's because you've only half-configured the system
for a weird timezone name. You would need an abbreviation entry as well
if you want "ETC/UTC" to be read as UTC-1.

For more info read
http://www.postgresql.org/docs/9.4/static/datatype-datetime.html#DATATYPE-TIMEZONES
--- see particularly the FOOBAR0 example, which is exactly what you've

Hi Tom,

thanks for the heads-up. I have to confess that I was chasing a ghost
here; the original report for the libpqtypes test failure I was
investigating was mentioning "Etc/GMT+12" (instead of UTC) which is a
legal zone, and with which the timestamptz output is correct even with
datestyle=postgres. I'll report back if the problem is on the PG or
libpqtypes side, sorry for the premature noise.

got here. By and large, I don't recommend use of POSIX notation in
timezone settings; the IANA geographical zone names are much less likely
to bite you on the rear.

The report is from Debian's reproducible builds project, they try to
make the package builds independent from as many environment factors as
possible, and one variation they try are timezones on the far plus or
minus side. Luckily, PostgreSQL itself is already reproducible (but
not cross-build friendly, more on that later), but libpqtypes got
caught by a TZ problem in the test suite.

https://wiki.debian.org/ReproducibleBuilds

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

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