CODEWITHSUNDEEP
sqlpostgresqltimestamp
doublebyte
doublebyte

Reputation: 1275

Converting a timestamp in milliseconds in Postgres

I have a timestamp in milliseconds:

1420066991000

That translates into UTC:

Wed Dec 31 2014 23:03:11

and local time:

Thu Jan 01 2015 00:03:11

However if I try to convert it into a timestamp in Postgres, using to_timestamp, it gives me a wrong datetime:

select to_timestamp(1420066991000);
46970-02-17 13:03:20+00

Since to_timestamp, expects a double precision input, I also did this:

select to_timestamp(1420066991000.0);
46970-02-17 13:03:20+00

but the results are the same.

Am I missing something in my Postgres configuration, like some timezone setting? or is it a bug?

Upvotes: 12

Views: 14336

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

to_timestamp() converts unix time, that is time in seconds. Since you have data in miliseconds you should divide it by 1000.

select to_timestamp(1420066991000/1000);

Upvotes: 20

Related Questions