How to alter table in Postgres to show Timestamp with Time Zone abbreviation (2004-10-19 10:23:54 EST)

Question

I want to alter my table row from 2011-06-30 05:59:59+00 format into 2011-06-30 05:59:59 CDT format

Answer 1

As Tim told, postgres does not store TZ info. You can't change column this way. Unless you create function or view or something (which won't be changing the table anyway). What you do instead, you change timezone to see your needed:

timezone (string)

Sets the time zone for displaying and interpreting time stamps. If not explicitly set, the server initializes this variable to the time zone specified by its system environment. See Section 8.5.3 for more information.

And use formatting to display TZ info... Like here:

b=# select now();
             now
-----------------------------
 2016-12-07 15:13:35.1369+00
(1 row)

b=# set timezone = EST;
SET

b=# select to_char(now(),'YYYY-MM-DD HH24:MI:SS TZ');
         to_char
-------------------------
 2016-12-07 10:13:55 EST
(1 row)