CODEWITHSUNDEEP
postgresqltype-conversionto-date
Arif
Arif

Reputation: 415

postgresql cast numeric to date and format

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

Upvotes: 3

Views: 31591

Answers (2)

adelagd
adelagd

Reputation: 23

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...

Upvotes: 0

mu is too short
mu is too short

Reputation: 434635

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.

Upvotes: 13

Related Questions