Dates and times (and timestamps) don't behave as expected

pgsql-bugs@postgresql.org writes:

My problem isn't very different from the ones already described, so I'll skip the description.

Why are you bothering to clog our inboxes with a completely
information-free complaint?

As you know if you've been following these bug reports, none of the
developers have been able to reproduce any such problem (at least not in
7.1). We need information about exactly what you're doing, exactly what
platform you're doing it on, exactly what timezone definition you're
using, etc etc. Nothing will get fixed otherwise.

regards, tom lane

Dates and times (and timestamps) don't behave as expected

...

For the most part, I get wrong results when using timestamps
and other date or time types and functions.

You MUST have more details here, since your vague problem description
simply cannot be attributed to PostgreSQL itself.

For the most part, a non-specific problem description such as yours
indicates a fundamental system problem which will be glaringly obvious
to even the casual observer or, less likely, a difference in
understanding how date and time should behave.

More info with examples please!!

- Thomas

(cc'd back to the -bugs list)

I'm truly sorry about this, shouldn't have posted that bug report, since it
was me, after a little research.

OK, thanks for following up!

I run the Mandrake 7.1 distribution, which was equipped with PostgreSQL 6.5,
or so I believe. I then upgraded to 7.0.3, through some Mandrake specific
RPMs. All was well. I noticed the timestamp error in that version, because I
wrote a little Perl DBI exercise and found out that I couldn't insert the
timestamp as I had gotten it from the backend, directly. It said "Bad
external representation". After overlooking it for a couple of hours (!), I
finally saw that the number of seconds were out of range, it was displayed
as 2001-04-29 00:00:60.00+02.

That is a known problem with the the way Mandrake built their RPMs for
PgSQL 7.0.x. There are RPMs at ftp.postgresql.org which have this
repaired.

I then looked in the bugtool database, and saw other users that had the same
problem. Also, using functions like date() on the timestamp seemed to make
matters worse, adding a day or even a month to the actual inserted timestamp
value upon output. I then decided to upgrade to the new PG 7.1 release, from
source. So I built it, and thought I had installed it correctly all the way.
I found out that the old backend was still running though, so there was now
a mix-up of versions. Since I corrected that, I've seen no trouble anymore
with the timestamps.

Great!

Sorry for wasting your valuable time with this faulty bugreport, I should've
tested more carefuly. Further, I want to compliment the development team,
another great job done with version 7.1, I'm hooked. Keep up the good work.

No problem; it is impossible to know exactly the right time to give up
on a problem search and report it to the list, and sometimes in
hindsight waiting longer or posting earlier would have been better.

Glad things are working well for you. btw, is it possible to update the
bugtool database with the problem resolution? If so, perhaps you could
do that when you have a chance.

- Thomas