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 <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
