PostgreSQL Error – Timestamp Out of Range Converting Epoch

postgresqlpostgresql-10timestamptype conversion

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):

select to_timestamp(1527012834506374);
ERROR:  timestamp out of range: "1.52701e+15"

Other approaches to conversion don't work either:

select 1527012834506374::abstime::timestamp;
ERROR:  cannot cast type bigint to abstime

select 1527012834506374::integer::abstime::timestamp;
ERROR:  integer out of range

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?

Best Answer

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:

select to_timestamp(1527012834506374 / 1000000.0)

Related Solutions

Postgresql – Creating sequence for longer numeric values

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:

create sequence my_sequence_name;
create table foo 
(
   id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
   constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);

That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.

PostgreSQL Date/Time Field Out of Range Error on Seconds-Since-Epoch

Seconds since epoch timestamps

Your "timestamp" is in seconds-since-epoch. Per dezso, use to_timestamp(). I missed that when I checked \dfS+

Bad complex idea, and dragons.

Check the input formats. They cover casts from strings. That's what COPY is doing under the hood. The only method that even remotely looks like a long number is ISO 8601. If you look at that example though you'll see it's not a seconds-since-epoch

Example    | Description
19990108   | ISO 8601; January 8, 1999 in any mode

This is basically the same as another example on that chart.

Example    | Description
1999-01-08 | ISO 8601; January 8, 1999 in any mode

Converting to `timestamp` with `abstime` as an intermediary format

So if you want to convert from seconds-since-epoch, you can cheat by using the internal abstime since there is no available cast directly to timestamp from a string of seconds-since-epoch.

SELECT 1421088300::abstime::timestamp;
      timestamp      
---------------------
 2015-01-12 12:45:00
(1 row)

What's happening here is that abstime is binary coercable with integer. You can see that in \dC+. I checked \dfS+ for functions to get from integer to timestamp and found none. There is a cast though from integer to abstime (which is stored as an integer), and from abstime to timestamp.

If this is a new table you could actually type that column as abstime. It should load perfectly fine. And then you can ALTER TABLE. here is an example, except I'm not running COPY (but it should work all the same).

CREATE TABLE foo(bar)
AS VALUES
  (1421088300::abstime);

TABLE foo;
          bar           
------------------------
 2015-01-12 12:45:00-06
(1 row)

ALTER TABLE foo
  ALTER bar
  TYPE timestamp;

TABLE foo;
         bar         
---------------------
 2015-01-12 12:45:00
(1 row)

\d foo;
                Table "public.foo"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 bar    | timestamp without time zone |