postgresql - How to convert a postgres database from CST to GMT? -

postgresql - How to convert a postgres database from CST to GMT? -

i setup postgres db installed on server in central time zone timestamp columns in central time zone.

is there way in postgres alter timezone columns in database cst gmt? best practice configure databases utilize gmt?

well, best practice avoid using timestamp type not know timezones , utilize timestamptz (short timestamp time zone) type store timestamps.

timestamptz stores both timestamp in utc , timezone in timestamp written (like cst, pst or gmt+6). allows manipulate , display these columns correctly, no matter current server or client timezone setting is.

you should able convert existing timestamp columns timestamptz using like:

alter table mytable alter column old_tstamp type timestamptz

before doing this, should experiment on little dataset or maybe little test table on how conversion timestamp timestamptz working such time zone info preserved on data.

if conversion not work correctly, can temporarily set timezone current session (you need conversion purposes) using statement (use before alter table ... column ... type):

set timezone 'cst6cdt';

or

set timezone 'utc+6';

this impact subsequent operations , conversions timestamp timestamptz - create sure right.

after have converted timestamps timestamptz, server or client timezone setting (which defaults current operating scheme timezone setting) useful display purposes, info manipulation correct.

postgresql