I have a column that is of type nullable timestamp without time zone. It is stored in my Postgres database in this format: 2021-06-24 11:00:00. And I would like to convert it to a nullable timestamp with time zone type such that it would be displayed as 2021-06-24 11:00:00.000-00. Notice that there is no timezone conversion.
The solution should also allow to convert from timestamp with time zone to timestamp without time zone. I did some research and was not able to find anything.
Advertisement
Answer
You’re looking for AT TIME ZONE, which is a little confusing but can do everything you need it to.
To convert a timestamp known to be UTC to a timestamptz:
SELECT '2021-06-24 11:00:00'::timestamp AT TIME ZONE 'UTC'; => 2021-06-24 06:00:00-05
All timestamptz values are stored internally in Postgres in UTC, and do not retain the timezone they were inserted at, so they will then show up in whatever your connection timezone is. You can cast a timestamptz back to a timestamp in your desired zone by using AT TIME ZONE again (as Erwin pointed out below, AT TIME ZONE always switches between timestamp and timestamptz):
SELECT '2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'UTC'; => 2021-06-24 11:00:00 SELECT '2021-06-24 11:00:00-00'::timestamptz AT TIME ZONE 'America/Chicago'; => 2021-06-24 06:00:00