Обсуждение: [SQL] Find rows with "timestamp out of range"

On 2017-06-19 09:11, Saiful Muhajir wrote:
> Hi,
> 
> 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
> ​While trying to​ figure out the rows containing the out of range
> value, I am using this with no result:
> 

Are you sure that CREATED_AT is a timestamp? It seems odd that
the database would contain an invalid timestamp value in a timestamp 
field.

Yes, I am sure.

 

                                              Table "public.comments"
     Column      │            Type             │                        Modifiers                   
─────────────────┼─────────────────────────────┼──────────────────────────────────────────────────────────────
 comment_id      │ bigint                      │ not null default nextval('comments_comment_id_seq'::regclass)
 user_id         │ bigint                      │ not null
 status          │ smallint                    │ not null default 1
 message         │ text                        │ not null
 create_time     │ timestamp without time zone │ not null default now()
 update_time     │ timestamp without time zone │

 

 

 

--

Regards,

Saiful Muhajir

 

On 19 June 2017 at 14:54, vinny <vinny@xs4all.nl> wrote:

On 2017-06-19 09:11, Saiful Muhajir wrote:

Hi,

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
​While trying to​ figure out the rows containing the out of range
value, I am using this with no result:
 

Are you sure that CREATED_AT is a timestamp? It seems odd that
the database would contain an invalid timestamp value in a timestamp field.

On 06/19/2017 12:11 AM, Saiful Muhajir wrote:
> Hi,
> 
> 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*
> 
> ​While trying to​ figure out the rows containing the out of range value, 
> I am using this with no result:
> 
> 
> *select comment_id, create_time from comments where create_time > '1 Jan 
> 9999';
> * comment_id │ talk_id │ create_time
> ────────────┼─────────┼─────────────
> (0 rows)
> 
> The error occurred when I query with:
> 
> 
> *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

Do you really have timestamps in create_time that are before '1 Jan 1800'?

To put it another way, what is the range of values you would expect for 
create_time?

> 
> 
> So, how do I figure out the rows with problems?

When you did the \copy and got the error, did it give you a row number 
for the error?

Have you ever had the database or computer crash?

> 
> 
> Thank you.
> 
> --
> Regards,
> 
> Saiful


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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