Search code examples
sqlpostgresqlepochsql-timestamp

Getting incorrect date when converting epoch to timestamp

I have a timestamp in epoch format like this: 1551187548876. If I convert this to a timestamp using https://www.epochconverter.com/, I get the correct time back.

However, if I convert it to time using PostgreSQL to_timestamp, I get the following:

select to_timestamp(1551187548876) from my_table;

51125-03-06 14:14:36+00

Question

How can I convert epoch 1551187548876 to a timestamp in PostgreSQL?

Solution

  • I assume that the epoch number you have is the number of milliseconds since the epoch, not the number of seconds, as customary.

    Try dividing by 1000:

    select to_timestamp(1551187548.876);

        to_timestamp        
----------------------------
 2019-02-26 14:25:48.876+01
(1 row)