On Thu, 21 Aug 2003, Tom Lane wrote:
> 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.
Yes. Timestamp with timezone is broken on the same boundaries in general.
I'm not really sure how to do better without some work, it seems we end up
with multiple different input values getting the same internal
representation so we can differentiate which version of the input was used
to get there (whether the user said 1901-12-13 23:00 or 1901-12-14).