postgresql - Need help to identify the actual date time format

Question

I have been struggling since few days to identify correct format for sample input date: 2020-01-30 14:39:25.022000 +00:00:00

Background :
I am working on data migration with postgres. And, have identified that in DB, the datetime related fields like 'modifiedAt' have datatype as varchar, which is bit weird. These records have values in above mentioned format.

I have fixed it in new postgres DB instance with datatype as timestamp. After migrating data, these fields are showing datetime in following format:

2020-01-30 14:39:25

How can I format above date to give output in this dateformat 2020-01-30 14:39:25.022000 +00:00:00?

Any help will be appreciated.

Answer 1

First you want to use timestamptz not timestamp. timestamptz does not actually store the time zone, it just makes the value time zone aware. For more information on this see:

Time Stamps 8.5.1.3. Time Stamps.

Second it looks like you did something like timestamp(0) which reduced the precision to whole seconds. If you leave the precision alone you get:

select '2020-01-30 14:39:25.022000 +00:00:00'::timestamptz;
         timestamptz         
-----------------------------
 01/30/2020 06:39:25.022 PST

-- I am in PST so the value gets rotated to that time zone for display.

-- If you want something closer to you desired output, then:

select to_char('2020-01-30 14:39:25.022000 +00:00:00'::timestamptz AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.USOF');
            to_char            
-------------------------------
 2020-01-30 14:39:25.022000+00