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.
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).
psql prints the timestamps, not with how postgresql stores them. You can do SET TIME ZONE 'Europe/Berlin' and then all subsequent queries in the same session will print timestamps in their textual representations in german time.SHOW TIME ZONE;'