On Thu, 21 Aug 2003, Stephan Szabo wrote:
> On Thu, 21 Aug 2003, Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone.bigpanda.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.
