Обсуждение: date out of range for timestamp
Dear postgresql.org members:
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:
Table "public.moms_outside_report"
Column | Type | Modifiers
----------------------------+-----------------------------+-----------
outreport_id | integer | not null
outreport_date | date |
outreport_pat_id | integer |
outreport_phys_id | integer |
outreport_report_id | integer |
outreport_date_reviewed | timestamp without time zone |
outreport_reviewed_by_u_id | integer |
guid | character varying(255) |
create_user | character varying(255) |
create_date | timestamp without time zone |
modify_user | character varying(255) |
modify_date | timestamp without time zone |
outreport_rg_id | integer |
outreport_name_id | integer |
image_path_name | character varying(255) |
Indexes:
"moms_outside_report_pkey" PRIMARY KEY, btree (outreport_id)
Foreign-key constraints:
"fka4c8b3652195c01a" FOREIGN KEY (outreport_reviewed_by_u_id)
REFERENCES auth_user(u_id)
"fka4c8b365275c337c" FOREIGN KEY (outreport_name_id) REFERENCES
moms_report_name(name_id)
"fka4c8b3658486426e" FOREIGN KEY (outreport_pat_id) REFERENCES
moms_patient(pat_id)
"fka4c8b3659ce19048" FOREIGN KEY (outreport_report_id) REFERENCES
moms_image_report(ireport_id)
"fka4c8b365c628e66a" FOREIGN KEY (outreport_phys_id) REFERENCES
moms_physician(phys_id)
"fka4c8b365e1c244a6" FOREIGN KEY (outreport_rg_id) REFERENCES
moms_radiograph(rg_id)
select count(*) from moms_outside_report where outreport_date <=
'12/10/2010';
ERROR: date out of range for timestamp
Now, the interesting thing is that this error is not given with any date
parameter prior to 12/10/2010. Also, I selected the entire table into a
temp table without constraints and could not reproduce the error. If
any one has any insight, I would be most appreciative.
--
David J. Rericha
OSS, LLC
Projects Manager
On Tue, Dec 28, 2010 at 5:02 PM, David Rericha <d.rericha@healthcareoss.com> wrote: > select count(*) from moms_outside_report where outreport_date <= > '12/10/2010'; > ERROR: date out of range for timestamp Try using an ISO date: select count(*) from moms_outside_report where outreport_date <= '2010-12-10'; I wonder if you've got a different date style set than you think. What does what does: psql mydb show datestyle; show?
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
[ please keep the list cc'd ]
David Rericha <d.rericha@healthcareoss.com> writes:
> I am truly typing the date as a literal. It turned out that I did have
> a date of year 2010000, which would have been out of range, but I
> changed it. But I still get the error.
Hmm. Try re-running ANALYZE on the table. I'm thinking that maybe
(a) the way-out-of-line date is still in the statistical histogram, and
(b) the planner is using date-to-timestamp coercion while trying to
estimate the selectivity of the <= condition.
regards, tom lane
Thanks, Tom. That was it. After I ran analyze on the table, the error disappeared. David J. Rericha OSS, LLC Projects Manager 563-650-5446 On 12/28/2010 08:45 PM, Tom Lane wrote: > [ please keep the list cc'd ] > > David Rericha <d.rericha@healthcareoss.com> writes: > >> I am truly typing the date as a literal. It turned out that I did have >> a date of year 2010000, which would have been out of range, but I >> changed it. But I still get the error. >> > Hmm. Try re-running ANALYZE on the table. I'm thinking that maybe > (a) the way-out-of-line date is still in the statistical histogram, and > (b) the planner is using date-to-timestamp coercion while trying to > estimate the selectivity of the <= condition. > > regards, tom lane > > >
David Rericha <d.rericha@healthcareoss.com> writes:
> Thanks, Tom. That was it. After I ran analyze on the table, the error
> disappeared.
Yeah, I just duplicated the failure here. It only happens if the
comparison value falls into the histogram bucket adjoining the
out-of-range value, which is why you didn't see it happen for some
values of the comparison constant. Doesn't look too hard to fix.
regards, tom lane
Tom, it seems to me that the planner's behavior in converting dates to timestamps in order to compare a date constant is a bug. I think I will submit it as such. If you enter a valid date into a field, you should be able to filter it even if that date exceeds the timestamp's range. Thanks again for your help. David J. Rericha OSS, LLC Projects Manager 563-650-5446 On 12/28/2010 09:06 PM, Tom Lane wrote: > David Rericha <d.rericha@healthcareoss.com> writes: > >> Thanks, Tom. That was it. After I ran analyze on the table, the error >> disappeared. >> > Yeah, I just duplicated the failure here. It only happens if the > comparison value falls into the histogram bucket adjoining the > out-of-range value, which is why you didn't see it happen for some > values of the comparison constant. Doesn't look too hard to fix. > > regards, tom lane > >