How to change a raw date to ISO date in postgres?-CodePudding

I have a table that I have loaded from MongoDB into a Postgres DB. One of the columns was in JSON that I parsed out. I now have a date column that is showing as a RAW date "1672690270000" and I would like to appear in ISO format "2023-01-02 20:11:10.000".

My current datestyle is postgres is ISO, MDY.

I tried to_date and to_timestamp,

Select TO_TIMESTAMP(startDate, 'YYYY/MM/DD HH24:MI:SS')

and recieved the same error for both,

ERROR: value for "YYYY" in source string is out of range DETAIL: Value must be in the range -2147483648 to 2147483647. SQL state: 22008

What does this error mean and how can I change this date?

CodePudding user response：

select to_char(to_timestamp('1672690270000'::bigint/1000), 
'YYYY/MM/DD HH24:MI:SS');

 to_char       
---------------------
 2023/01/02 12:11:10

Convert string '1672690270000' seconds and then pass to to_timestamp() from here DateTime functions then format to ISO using to_char from here Data formatting.