Error timestamp out of range

[SQL] Find rows with "timestamp out of range"

Re: [SQL] Find rows with "timestamp out of range"

Re: [SQL] Find rows with "timestamp out of range"

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

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.

Re: [SQL] Find rows with "timestamp out of range"

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

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.

Re: [SQL] Find rows with "timestamp out of range"

Re: [SQL] Find rows with "timestamp out of range"

Re: [SQL] Find rows with "timestamp out of range"

Saiful Muhajir 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 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 from comments where create_time and see if that is able to produce output.

I am creating a Date object from a String in which the year is 2015123 and then trying to save it to Postgres. It’s creating a Date object properly in the Java code but giving error timestamp out of range while saving it to the database.

It’s converting a date object like this.

While I am trying to save it in Postgres it gives me an error.

I want to validate the Date object in java. Is there any possibility to validate in Java in spite of the Postgres error?

I’m getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table):

Other approaches to conversion don’t work either:

It’s a valid epoch; https://www.epochconverter.com/ tells me 1527012834506374 is equivalent to 2018-05-22 06:13:54.506 UTC

How can I do the conversion using SQL in Postgres?

1 Answer 1

When I paste the value 1527012834506374 into https://www.epochconverter.com/ I see the warning:

Assuming that this timestamp is in microseconds (1/1,000,000 second):

Postgres’ to_timestamp() assumes an epoch with seconds, not microseconds, so you need to use:


[an error occurred while processing the directive]
Карта сайта