Re: BUG #16027: Invalid output of to_timestamp

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: jeff(dot)sturm(at)helloworld(dot)com
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16027: Invalid output of to_timestamp
Date: 2019-09-26 15:39:17
Message-ID: 13930.1569512357@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> With a date in year 1904 or earlier, the to_timestamp function outputs
> unexpected characters:

> template1=# select to_timestamp('19041231','YYYYMMDD');
> to_timestamp
> ------------------------------
> 1904-12-31 00:00:00-05:32:11

> Expected is "1904-12-31 00:00:00-06"

There's nothing particularly unexpected about that.
I surmise that you're using the America/Detroit zone,
because nothing else matches that UTC offset, and what
the IANA timezone database has for Detroit is

# Zone NAME STDOFF RULES FORMAT [UNTIL]
Zone America/Detroit -5:32:11 - LMT 1905
-6:00 - CST 1915 May 15 2:00

with some commentary explaining why they think Detroit adopted
standard time in 1905 rather than some other year. Before
standardized time, most places observed local solar time, so
tzdb provides an estimate of what the UTC offset would have
been for that in Detroit.

If this discombobulates you, you can edit your local copy of
the tzdb files; but it's not wrong from our standpoint.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jeffrey Sturm 2019-09-26 16:03:42 RE: BUG #16027: Invalid output of to_timestamp
Previous Message PG Bug reporting form 2019-09-26 15:20:56 BUG #16027: Invalid output of to_timestamp