Re: pg_dump/pg_restore with time zone displacement out of range

Patric Bechtel <patric(dot)bechtel(at)gmail(dot)com> writes:
> My platform: Ubuntu 12.04, PostgreSQL 9.2beta1, tried it with 9.1 with same result.
> postgresql.conf is stock, except for timezone, which is set to Asia/Manila.

> $ createdb -U postgres tztest
> $ psql tztest postgres
> tztest=# create table foo (bar int, baz timestamptz);
> CREATE TABLE
> tztest=# insert into foo values (1,'2012-05-01T14:00:00');
> INSERT 0 1
> tztest=# insert into foo values (2,'2012-05-01T14:00:00 bc');
> INSERT 0 1
> tztest=# select * from foo;
> bar | baz
> - -----+------------------------------
> 1 | 2012-05-01 14:00:00+08
> 2 | 2012-05-01 14:00:00-15:56 BC

> Now that's strange already.

No, it's perfectly normal, per the Olson timezone database info for
Manila:

# Zone NAME GMTOFF RULES FORMAT [UNTIL]
Zone Asia/Manila -15:56:00 - LMT 1844 Dec 31
8:04:00 - LMT 1899 May 11
8:00 Phil PH%sT 1942 May
9:00 - JST 1944 Nov
8:00 Phil PH%sT

The Olson database generally uses local mean solar time for the named
city as the reference point in years before that locality adopted any
official standard time reference. Very few places are at an exact hour
offset from Greenwich, so you see all sorts of weird UTC offsets there.
In this case it looks like Manila changed their minds as to whether to
reckon themselves west or east of Greenwich back in 1844.

> tztest=# \i tztest(at)pht(dot)sql
> [...]
> psql:tztest(at)pht(dot)sql:20: ERROR: time zone displacement out of range: "2012-05-01 14:00:00-15:56 BC"
> KONTEXT: COPY foo, line 2, column baz: "2012-05-01 14:00:00-15:56 BC"

This, on the other hand, is unfortunate. If we're going to dump the UTC
offset that way, we surely ought to reload it. I've not looked at the
code yet, but from memory we allow +/-14 hours as a sane range. Looks
like maybe we'd better run through all the Olson entries and see what is
least sane ...

Will fix, thanks for the report.

regards, tom lane