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