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

mu is too short · Accepted Answer · 2014-10-05 00:30:41Z

13

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.

answered Oct 5, 2014 at 0:30

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JGS Over a year ago

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 Over a year ago

what is :: here in the above casting?

mu is too short Over a year ago

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

JGS Over a year ago

when I inserted a record in the existing database table it inserted this value as 1505468541312 for the current date

mu is too short Over a year ago

@JGS How specifically did you insert it? When did you insert it?

adelagd · Accepted Answer · 2018-12-17 16:11:22Z

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

answered Dec 17, 2018 at 16:11

adelagd

239 bronze badges

Collectives™ on Stack Overflow

postgresql cast numeric to date and format

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related