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

On Mon, 25 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > On Thu, 21 Aug 2003, Tom Lane wrote:
> >> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> >>> Wait, he's in australia, what if he's getting the edge case the other way.
> >>
> >> 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).
>
> I've fixed to_date() along the above lines, but the general problem of
> how timestamp I/O should behave remains.
>
> I've come to the conclusion that there isn't any really consistent
> behavior if we want to stick with the current definition that
> "timestamps outside the Unix date range are always UTC". If we do that,
> then there is a set of timestamps at one end of the date range that are
> ambiguous (they could be taken as either UTC or local), while at the
> other end of the range there is a set of timestamps that can't be
> validly converted as either one. This is essentially the same problem
> we have during daylight-savings transition hours: when you "spring
> forward" there is no local time 02:30, and when you "fall back" there
> are two of 'em.
>
> The solution we've adopted for DST transitions is to interpret invalid
> or ambiguous local times as "always standard time". We could possibly
> do the same for the questionable times at the ends of the Unix date
> range, ie, always interpret them as UTC (although I've been fooling with
> the code for a couple hours now trying to get it to do that, without
> much success).

Yeah, it seemed like the rules involved in doing that might be complicated
to get right.

> Plan B would be to get rid of the discontinuity by abandoning the rule
> that timestamps outside the Unix range are UTC. We could instead say
> that the local time zone offset that mktime() reports for the first date
> of the Unix range applies to all prior dates, and similarly the offset
> for the last date of the range applies to all later dates.
>
> I'm unsure which of these is a better answer. Any thoughts?

Generally, I think B is best since it keeps the values more continuous and
doesn't require complicated trickery, although I'm not sure if that might
change the observable behavior for people using timestamps outside the
boundaries currently. I'm not one of them, so maybe we should continue on
-general?