Re: postgresql 7.3.2 bug on date '1901-12-13' and '1901-12

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
>>> This still doesn't explain why Arnold sees a failure with to_date and
>>> we don't, though.

> Wait, he's in australia, what if he's getting the edge case the other way.
> It starts out on the 14th, does the timezone conversion. But then it
> looks like it's on the 13th which doesn't have timezone info and doesn't
> do the timezone conversion back.

Bingo.

regression=# show time zone;
TimeZone
----------
EST5EDT
(1 row)

regression=# select to_date('1901/12/14', 'YYYY/MM/DD');
to_date
------------
1901-12-14
(1 row)

regression=# set time zone 'CST-9:30CDT';
SET
regression=# select to_date('1901/12/14', 'YYYY/MM/DD');
to_date
------------
1901-12-13
(1 row)

It looks like the same result occurs in any time zone east of
Greenwich.

Looking at the code, the problem seems to be that to_date is built as
timestamptz_date(to_timestamp(str,fmt))

The initial step yields

regression=# select to_timestamp('1901/12/14', 'YYYY/MM/DD');
to_timestamp
---------------------
1901-12-13 23:00:00
(1 row)

and then timestamptz_date quite reasonably yields 1901-12-13.

I'm inclined to fix to_date by decomposing the code differently ---
it should avoid the coercion to timestamp, which is a waste of cycles
anyway. But is to_timestamp (and more generally timestamp's input
converter) broken? If so, how can we do better? I don't think we can
entirely avoid the problem of a transition between local and GMT time.

regards, tom lane