Get date in postgreSQL

Question

I have a column with type numeric which consist date in timestamp format. I need to get a date in 'YYYY-MM-DD HH:mm:ss' format. My code:

select start_dt_timestamp, to_timestamp(start_dt_timestamp/1000) as dt 
  from tmp_table

In result:

start_dt_timestamp	dt
1554747217660018	51237-12-23 16:07:40

51237-12-23 16:07:40 - is not correct date. What I need to do get correct answer?

you should show what start_dt_timestamp value is for understand what is wrong — HackeMate
– HackeMate, Commented Jun 20, 2021 at 11:45
Is the /1000 correct, or should it be /1000000 or something? — Jiří Baum
– Jiří Baum, Commented Jun 20, 2021 at 11:47

Jiří Baum · Accepted Answer · 2021-06-20 12:01:03Z

1

The column is in microseconds, not milliseconds; divide it by 1000000 instead of 1000:

select start_dt_timestamp, to_timestamp(start_dt_timestamp/1000000) as dt 
  from tmp_table

That should give 2019-04-09 04:13:37

answered Jun 20, 2021 at 12:01

Jiří Baum

6,9882 gold badges19 silver badges19 bronze badges

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

2 Comments

BTW to_timestamp(start_dt_timestamp/1000000.0) will produce the more accurate value

Or, if rounding to whole seconds is desired: to_timestamp((start_dt_timestamp+500000)/1000000)