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

Stephan Szabo <sszabo@megazone.bigpanda.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