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

Bob Soeters (bob@iway.nl) reports a bug with a severity of 1
The lower the number the more severe it is.

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

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

This is devastating, since I rely on timestamps to get some functionality covered.

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

Sample Code


No file was uploaded with this report

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