agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFind rows with "timestamp out of range"
8+ messages / 5 participants
[nested] [flat]
* Find rows with "timestamp out of range"
@ 2017-06-19 07:04 Saiful Muhajir <saifulmuhajir@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Saiful Muhajir @ 2017-06-19 07:04 UTC (permalink / raw)
To: pgsql-sql
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
So, how do I figure out the rows with problems?
Thank you.
--
Regards,
Saiful
^ permalink raw reply [nested|flat] 8+ messages in thread
* Find rows with "timestamp out of range"
@ 2017-06-19 07:11 Saiful Muhajir <saifulmuhajir@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Saiful Muhajir @ 2017-06-19 07:11 UTC (permalink / raw)
To: pgsql-sql
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
So, how do I figure out the rows with problems?
Thank you.
--
Regards,
Saiful
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Find rows with "timestamp out of range"
@ 2017-06-19 07:54 vinny <vinny@xs4all.nl>
parent: Saiful Muhajir <saifulmuhajir@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: vinny @ 2017-06-19 07:54 UTC (permalink / raw)
To: Saiful Muhajir <saifulmuhajir@gmail.com>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Find rows with "timestamp out of range"
@ 2017-06-19 07:57 Saiful Muhajir <saifulmuhajir@gmail.com>
parent: vinny <vinny@xs4all.nl>
0 siblings, 1 reply; 8+ messages in thread
From: Saiful Muhajir @ 2017-06-19 07:57 UTC (permalink / raw)
To: vinny <vinny@xs4all.nl>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
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 <http://saifulmuhajir.web.id;
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Find rows with "timestamp out of range"
@ 2017-06-19 08:37 Samed YILDIRIM <samed@reddoc.net>
parent: Saiful Muhajir <saifulmuhajir@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Samed YILDIRIM @ 2017-06-19 08:37 UTC (permalink / raw)
To: Saiful Muhajir <saifulmuhajir@gmail.com>; vinny <vinny@xs4all.nl>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org>
<div>Hi Saiful,</div><div> </div><div>Which version of PostgreSQL do you use?</div><div>Have you ever upgrade your Postgres from earlier major releases?</div><div>What was your upgrade method (dump/restore, pg_upgrade)?</div><div> </div><div>Best regards.</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>19.06.2017, 10:59, "Saiful Muhajir" <saifulmuhajir@gmail.com>:</div><blockquote type="cite"><div><div style="font-family:arial,helvetica,sans-serif;">Yes, I am sure.</div><div style="font-family:arial,helvetica,sans-serif;"> </div><div style="font-family:arial,helvetica,sans-serif;"> Table "public.comments"<br /> Column │ Type │ Modifiers <br />─────────────────┼─────────────────────────────┼──────────────────────────────────────────────────────────────<br /> comment_id │ bigint │ not null default nextval('comments_comment_id_seq'::regclass)<br /> user_id │ bigint │ not null<br /> status │ smallint │ not null default 1<br /> message │ text │ not null<br /> create_time │ timestamp without time zone │ not null default now()<br /> update_time │ timestamp without time zone │</div><div style="font-family:arial,helvetica,sans-serif;"> </div><div style="font-family:arial,helvetica,sans-serif;"> </div></div><div> <div><div><div><div><div><div><font color="#666666" size="2" face="tahoma, sans-serif">--</font></div><font color="#666666" size="2" face="tahoma, sans-serif">Regards,<br /><br /><a target="_blank" href="http://saifulmuhajir.web.id/">Saiful Muhajir</a></font></div></div></div></div></div> <div>On 19 June 2017 at 14:54, vinny <span><<a target="_blank" href="mailto:vinny@xs4all.nl">vinny@xs4all.nl</a>></span> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><span>On <span>2017-06-19 09</span>:11, Saiful Muhajir wrote:</span><blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;">Hi,<br /><br />I have a table with around 133 MILLION ROWS with two timestamp<br /><span>columns. While trying to copy some columns for a new database, using</span><br />\COPY , the error occurred with: TIMESTAMP OUT OF RANGE<br /><span>While trying to figure out the rows containing the out of range<br />value, I am using this with no result:</span><br /> </blockquote><br />Are you sure that CREATED_AT is a timestamp? It seems odd that<br />the database would contain an invalid timestamp value in a timestamp field.</blockquote></div></div></blockquote>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Find rows with "timestamp out of range"
@ 2017-06-19 13:25 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Saiful Muhajir <saifulmuhajir@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2017-06-19 13:25 UTC (permalink / raw)
To: Saiful Muhajir <saifulmuhajir@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Find rows with "timestamp out of range"
@ 2017-06-19 13:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Saiful Muhajir <saifulmuhajir@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2017-06-19 13:33 UTC (permalink / raw)
To: Saiful Muhajir <saifulmuhajir@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Find rows with "timestamp out of range"
@ 2017-06-20 01:48 Saiful Muhajir <saifulmuhajir@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Saiful Muhajir @ 2017-06-20 01:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 <http://saifulmuhajir.web.id;
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2017-06-20 01:48 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-19 07:04 Find rows with "timestamp out of range" Saiful Muhajir <saifulmuhajir@gmail.com>
2017-06-19 07:11 Find rows with "timestamp out of range" Saiful Muhajir <saifulmuhajir@gmail.com>
2017-06-19 07:54 ` vinny <vinny@xs4all.nl>
2017-06-19 07:57 ` Saiful Muhajir <saifulmuhajir@gmail.com>
2017-06-19 08:37 ` Samed YILDIRIM <samed@reddoc.net>
2017-06-19 13:25 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-06-19 13:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-20 01:48 ` Saiful Muhajir <saifulmuhajir@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox