From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rericha <d.rericha@healthcareoss.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: date out of range for timestamp
Date: Tue, 28 Dec 2010 20:36:20 -0500
Message-ID: <13702.1293586580@sss.pgh.pa.us> (raw)
In-Reply-To: <4D1A7AB3.4010008@healthcareoss.com>
References: <4D1A7AB3.4010008@healthcareoss.com>
David Rericha <d.rericha@healthcareoss.com> writes:
> I have a 21 GB database in version 8.4.5 that is giving me a curious
> error when I perform a query on one of the tables:
> select count(*) from moms_outside_report where outreport_date <=
> '12/10/2010';
> ERROR: date out of range for timestamp
Did you really truly type the query just like that, or was the
comparison "constant" actually a placeholder of some sort?
The only way I can see for that query to invoke the places where
that error message can be produced is if what you were really
doing was "date_column <= timestamp_constant". And an unmarked
literal string being compared to a date column would *not* get
interpreted as a timestamp. But if you were issuing this through
some client-side driver that was marking the parameter as being of
type timestamp, it's believable.
Anyway, assuming that the query really is date <= timestamp, the problem
is you've got some wacko date value in the table, and when the backend
tries to promote that date to timestamp so it can do the comparison, it
fails. Per the fine manual, dates go out to about 5 million AD while
timestamps only reach about 300 thousand AD. Try
select * from moms_outside_report where outreport_date >
'290000-01-01'::date or outreport_date < '4700-01-01 BC'::date;
to find the bad values. Or maybe better, search for anything outside
the range of what you think the entries ought to be.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, d.rericha@healthcareoss.com
Subject: Re: date out of range for timestamp
In-Reply-To: <13702.1293586580@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox