Thread: invalid abstime to timestamp error only on specific date range
I have a postgres table with a column type abstime. (all data in this database are loaded from an ancient propriety database) When I filter data from this table with a specific date range on this column it works. When I filter with a different date range it gives me an error like invalid abstime to timestamp I don't have that server at this moment and I cannot post exact error. I'll if I have to. I was pulling my hair out because it happens only with a specific date range and can't be my invalid format in select statement. Say i do psql> select * from shipping where ship_date <= '04/22/2008' and ship_date>='04/21/2008' it works but if i do psql> select * from shipping where ship_date <= '04/22/2010' and ship_date>='04/21/2010' it won't even perform the query but emits the error. Both server and client are 8.4.3 I tried other date formats but above format should work because it works with 2008 year. Thanks for any suggestion
zhong ming wu <mr.z.m.wu@gmail.com> writes: > I have a postgres table with a column type abstime. (all data in this > database are loaded from an ancient propriety database) > When I filter data from this table with a specific date range on this > column it works. When I filter with a different date range > it gives me an error like > invalid abstime to timestamp > I don't have that server at this moment and I cannot post exact error. The closest thing I can find to that in the code is cannot convert abstime "invalid" to timestamp The abstime type does have a special reserved value "invalid", while timestamp doesn't so that value can't be converted to timestamp. I'm guessing that you have an "invalid" or two lurking in the table somewhere, but it's hard to be specific with so few details. You've not shown us enough information to tell why your query would be trying to convert any abstime values to timestamp --- let alone that particular one. regards, tom lane
On Tue, Apr 27, 2010 at 11:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > zhong ming wu <mr.z.m.wu@gmail.com> writes: > > The closest thing I can find to that in the code is > > cannot convert abstime "invalid" to timestamp Yes this is the message. There were invalid values in that table at first. Then I deleted such entries and tried the same query and got the same message. The query is four table joins but in order to pin-point the problem I pruned it down to as simple as what I posted originally. In any case I reloaded the data and on this second pass I stopped receiving the error. The reloading scripts were different and I don't know what that did to the reloaded data. Thanks