PostgreSQL epoch time conversion

问题:

I am using the postgresSQL function to_timestamp(double precision) to convert from epoch time to normal timestamp but I am facing a problem where the timestamp is incorrect

SELECT to_timestamp(1428058548491);

produces "47223-05-17 12:08:11.000064+02"

while it should be 4/3/2015, 12:55:48 PM GMT+2:00 DST

SELCT to_timestamp(1428058557697);

produces "47223-05-17 14:41:36.999936+02"

while it should be 4/3/2015, 12:55:57

as can be seen the dates have been converted totally incorrect

回答1:

As was explained from people In the comments for people who get the same problem. The function to_timestamp() expects it in seconds not milliseconds therefore that is the solution.

Quote from the manual:

it accepts a double precision argument and converts from Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone