Postgres changing data type from timestamp to timestamptz (with time zone)

Question

I recognize there are some similar questions, and I've read quite a few, so pardon me if I just missed the answer.

I have data that was stored as a TIMESTAMP data type, but now we want it to be stored as a TIMESTAMPTZ. I am running an ALTER statement that takes care of the conversion, however it seems to have an implied timezone that the data is stored in. Interestingly enough the data is stored in UTC time, via the ETL processes:

Sample data - current time as of run is 2023-09-27 13:51:09

-- +---+------------------------+
-- |id |start_date
-- +---+------------------------+
-- |0  |2023-09-27 19:51:09:953 |
-- |1  |2023-09-27 19:51:09:953 |

You can see I'm 6 hours behind UTC, which is fine and dandy. However, when I convert the column data type to a TIMESTAMPTZ (alter table test_timezone alter column start_date type timestamptz;) I get the following:

-- +---+------------------------------+
-- |id |start_date
-- +---+------------------------------+
-- |0  |2023-09-27 19:51:09:953 -0600 |
-- |1  |2023-09-27 19:51:09:953 -0600 |

So it either doesn't recognize that it's UTC time, or thinks it's in my America/Denver timezone. I can get the correct timezone by running the following: alter table test_timezone alter column updated_at type timestamptz using updated_at at time zone 'UTC';:

-- +---+------------------------------+
-- |id |start_date
-- +---+------------------------------+
-- |0  |2023-09-27 13:51:09:953 -0600 |
-- |1  |2023-09-27 13:51:09:953 -0600 |

However my desired results are to retain the UTC datetimes in the database, just display the correct timezone of the time stored:

-- +---+------------------------------+
-- |id |start_date
-- +---+------------------------------+
-- |0  |2023-09-27 19:51:09:953 +0000 |
-- |1  |2023-09-27 19:51:09:953 +0000 |

(I'm not sure if that's how the actual UTC time is displayed, as I can't get it to display that way in my database, but the general idea of the desired results should be clear enough there.)

Answer 1

I think I figured it out on my own. Unfortunately for me I use a software for SQL that was trying to be friendly by converting all timezones to my current timezone, after querying the data in a different software, the data was in fact in the format I wanted after I ran the following alter statement: alter table test_timezone alter column updated_at type timestamptz using updated_at at time zone 'UTC';

After that, it was just a manner of my IDE trying to be friendly and convert it for me in my timezone. Need to figure out how to change that, but I figured it out. Hopefully the alter statement helps someone else out!