Converting a timestamp in milliseconds in Postgres

Question

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?

Accepted Answer

`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);
```

Converting a timestamp in milliseconds in Postgres

Solution

Problem