Request a Demo
Home   >   mailing lists

Re: [SQL] Find rows with "timestamp out of range" - Mailing list pgsql-sql

From Saiful Muhajir
Subject Re: [SQL] Find rows with "timestamp out of range"
Date
Msg-id CAA0dH_ucbGWSZheewb-oiSi3_H4+fOwjm=0ypoBmwOHCsR+_6Q@mail.gmail.com
Whole thread
In response to Re: [SQL] Find rows with "timestamp out of range"  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
Tree view
Hi,

I tried with

SELECT comment_id FROM comments WHERE create_time < '1 Jan 1800';

But the result is same: ERROR 22008 timestamp out of range.

With the help from #postgresql community, I successfully extract the min(create_time) with below query:

SELECT min(trim(leading '\' from timestamp_send(create_time)::text)::bit(64)::bigint) FROM comments;
min
-------
-332024613738615000

Which is in microseconds from 2000-01-01 00:00:00 and the result is correspond to ~10500 BC. Way out of range. CMIIW

So, while trying to figure out the "broken" rows with this query:

SELECT comment_id FROM comments WHERE trim(leading '\' from timestamp_send(create_time)::text)::bit(64)::bigint > -31556908800000000

And there are many rows, 800+. So, I guess this looks like corrupted data in the table.

A while ago, the server was crashed when our datacenter experienced power outage. But I didn't checked anything until today. It seems that what's left is how do I fix this because we don't store old backups.

For your information, I forgot to mention that this is Postgres 9.3.15 with fsync=ON.




--
Regards,

Saiful Muhajir

On 19 June 2017 at 20:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Saiful Muhajir <saifulmuhajir@gmail.com> writes:
> I have a table with around *133 million rows* with two timestamp columns.
> While trying to copy some columns for a new database, using *\COPY *, the
> error occurred with: *timestamp out of range*

> *select comment_id, create_time from comments where create_time < '1 Jan
> 1800';*
> ERROR:  22008: timestamp out of range
> LOCATION:  timestamp_out, timestamp.c:226

As you can see, the error is occurring in timestamp_out(), ie in the
attempt to display the specific value.  You could probably do this
successfully:

select comment_id from comments where create_time < '1 Jan 1800';

and to fix, maybe

update comments set create_time = '-infinity' where create_time < '1 Jan 1800';


As to what's actually going on, we made an effort a few years back to
tighten up the logic concerning exactly what is the minimum legal
timestamp value --- it's somewhere in 4714BC, but as I recall, the exact
boundary where it failed used to depend on your TimeZone setting.  (Maybe
it still does, for you ... what PG version is this exactly?)  I'm betting
that you have a value right on the hairy edge of failure, that was
accepted when input but is now rejected during display, either because of
the aforesaid logic changes or because you're using a different TimeZone
setting than it was input under.

It might be entertaining to try

select comment_id, create_time + interval '1 year'
from comments where create_time < '1 Jan 1800';

and see if that is able to produce output.

                        regards, tom lane

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: [SQL] Find rows with "timestamp out of range"
Next
From: Ed Rouse
Date:
Subject: [SQL] Finding the negative