postgresql: Change timestamp timezone part without shifting hours?

Question

I have a timestamp field in my table that has stored the data in UTC-10. However, the correct timezone is UTC+10

I would like to change the timezone part to UTC+10 without shifting the hours. Only want the system to consider the same timestamp as UTC+10 instead of UTC-10

If I do it like

select email_timestamp as time zone 'UTC+10' from emails

then it also shift the hours.

Can someone please tell me how to change the timezone part without shifting the hours?

Answer 1

Strip off the time zone without changing the time, then add a timezone without converting the time:

(myfield::timestamp || 'UTC+10')::timestamptz

Select it again for your timezone to see that's what you wanted:

SELECT myfield AT TIME ZONE 'UTC+10'