PostgreSQL : Converting timestamp without time zone to a specific timezone not working

Question

I am trying to migrate a column in Postgres from Timestamp without time zone to a timestamp with time zone. I want to do it for German time, so I used the following query :

alter table table_name alter column uploaddate type TIMESTAMP WITH TIME ZONE USING uploaddate at time zone 'UTC+01:30';

Unfortunately it's not working, it's adding 2015-06-30 07:30:48.785+05:30. I am currently in India, which is +5.30. How can I specify to the query to do it with German time zone. Thank you.

Answer 1

What is the timezone of the timestamps stored in the table? That is, if there is a value such as 2016-09-22 07:30 in the table, in what timezone is 07:30? This is the timezone that you want to use, not your current local timezone. So e.g. if all timestamps are expressed in german timezone you should do something like:

alter table table_name
alter column uploaddate type TIMESTAMP WITH TIME ZONE
USING uploaddate at time zone 'Europe/Berlin';

Don't forget that you can run the above inside a transaction so that you can inspect the results before you commit them (of course this will lock the entire table and block all other concurrent queries for that table).