3

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

Arif
  • 377
  • 2
  • 5
  • 21

2 Answers2

12

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • the date fomat came correct but still the date converted is coming wrong.. to_timestamp(1505468541312:numeric/1000) ..... it should be currentdate i.e. 2017-09-25 ....but it coming 2017-09-15 – JGS Sep 25 '17 at 10:51
  • what is :: here in the above casting? – JGS Sep 25 '17 at 11:01
  • @JGS Yes, `::` is a typecast, same thing as `cast(1401710352000 as numeric)`, otherwise the `/` would be integer division. Why would you expect `1505468541312` to be the current date? The current date in the above format would be `1506297600000`. – mu is too short Sep 25 '17 at 17:34
  • when I inserted a record in the existing database table it inserted this value as 1505468541312 for the current date – JGS Sep 26 '17 at 05:00
  • @JGS How specifically did you insert it? When did you insert it? – mu is too short Sep 26 '17 at 05:03
0

I had the field 'fecha' in seconds.. and I had to converted it to date to be able to compare it (formatting it). I did it like that this:

select to_char(to_timestamp(fecha),'DD/MM/YYYY') from...
adelagd
  • 33
  • 9