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

On Thu, 21 Aug 2003, Stephan Szabo wrote:

> On Thu, 21 Aug 2003, Stephan Szabo wrote:
>
> > On Thu, 21 Aug 2003, Tom Lane wrote:
> >
> > > Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > > > Hmm, I just got my machine to give a similar failure mode with
> > > > a slightly wacky input.
> > >
> > > Perhaps more to the point:
> > >
> > > regression=# select timestamptz '1901/12/13 0:0:0';
> > > timestamptz
> > > ---------------------
> > > 1901-12-13 00:00:00
> > > (1 row)
> > >
> > > regression=# select timestamptz '1901/12/14 0:0:0';
> > > timestamptz
> > > ------------------------
> > > 1901-12-14 00:00:00-05
> > > (1 row)
> > >
> > > Note the lack of timezone in the first output.
> > >
> > > It looks like 1901/12/14 is the oldest date for which the system will
> > > return timezone information; IIRC, this is the oldest date representable
> > > as a 32-bit time_t. PG implicitly assumes that timestamps before that
> > > are always GMT.
> >
> > In my case the 23:59:59.99999 vs .99999999999 means that in one case the
> > system correctly determines that there's no timezone. In the latter, it
> > thinks there's no timezone on input (because it hasn't added the
> > fractional seconds), but that ends up rounding up so that on output it
> > thinks it's on the 14th and therefore has timezone info (the
> > IS_VALID_UTIME check) and does the timezone thus giving back a time on the
> > 13th with a timezone. It's basically a wierd edge case we get wrong.
> >
> > > This still doesn't explain why Arnold sees a failure with to_date and
> > > we don't, though.
> >
> > I think it comes from (from his machine)
> >
> > db1=> select timestamptz '1901/12/14';
> > timestamptz
> > ---------------------
> > 1901-12-13 13:00:00
> > (1 row)
>
> 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.

And, in fact, when I set my machines timezone to an australian one (not
postgres because that seems to follow a separate path) I get precisely
that behavior. The timezone conversion is done on input but not on
output. I'm not really sure how to fix it though.