postgresql cast numeric to date and format

Question

I have my date field as numeric in postgresql and the values in the date are like 1401710352000 and when I try to convert it using to_timestamp(date) then I get the corresponding timestamp as "46388-06-07 10:40:00+00"

I have tried to google it and I have found quite useful functions on how to do different type conversions in postgresql but i could not find how to cast a numeric date to a proper readable format. i will apprecaite if someone can tell me how to convert a numeric date field to a timestamp/date readable format

Shah

Answer 1

From the fine manual:

to_timestamp(double precision)
timestamp with time zone
convert Unix epoch to time stamp

A Unix epoch is in seconds but it looks like your numeric value is in milliseconds. If we fix the units, then we get something that looks reasonable:

=> select to_timestamp(1401710352000::numeric/1000);
      to_timestamp      
------------------------
 2014-06-02 04:59:12-07

So you probably just need to fix the units by dividing by 1000 before you call to_timestamp.