agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Saiful Muhajir <saifulmuhajir@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Find rows with "timestamp out of range"
Date: Mon, 19 Jun 2017 09:33:10 -0400
Message-ID: <19153.1497879190@sss.pgh.pa.us> (raw)
In-Reply-To: <CAA0dH_v08fBFWp+4TsRz8wESSp8zgKCL_Sdc-OVdA3vQd_XjUg@mail.gmail.com>
References: <CAA0dH_v08fBFWp+4TsRz8wESSp8zgKCL_Sdc-OVdA3vQd_XjUg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (8+ messages)  latest in thread

Message-ID: <19153.1497879190@sss.pgh.pa.us>
Permalink:  ../19153.1497879190@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/19153.1497879190@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, saifulmuhajir@gmail.com
  Subject: Re: Find rows with "timestamp out of range"
  In-Reply-To: <19153.1497879190@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox