4

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?

5

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)
  • 1
    Agreed, by definition the Unix epoch is a number of seconds in a 32 bits signed integer, hence the maximum value possible is 2 147 483 647, anything higher than that is something else, like the epoch padded with 3 digits of milliseconds or 6 digits of microseconds. – Patrick Mevzek Dec 19 '18 at 16:09
  • 2
    Make that to_timestamp(1527012834506374 / 1000000.0) to avoid integer division truncating fractional digits. – Erwin Brandstetter Dec 19 '18 at 16:48
  • @PatrickMevzek And I was wondering why the numbers I had looked so big. They come from ejabberd's archive table. Spot the DBA here that never stores dates and times as numbers :-) – Colin 't Hart Dec 19 '18 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.