Postgresql update timestamp without timezone to have timezone

datetimepostgresqltimestamp

When I created the table, a Timestamp column without timezone was created. All the imported time is in UTC. Now I want to add the timezone information explicitly, and I have altered the column

ALTER TABLE review ALTER COLUMN review_time TYPE TIMESTAMP WITH TIME ZONE USING review_time AT TIME ZONE 'UTC';

Selecting the existing data does not show the added timezone information

review_time
2017-07-28 02:25:44
2017-07-28 03:10:35
2017-07-28 03:11:32
2017-07-28 03:11:35
2017-07-28 03:11:38
2017-07-28 03:11:41
2017-07-28 18:54:54

Do I need to run an UPDATE statement on the existing data, and if so, what is the syntax?

Update 1

The output that is missing timezone information is due to the application itself (SQLWorkbenchJ). Querying from psql will show the timezone

mydb # SELECT review_time FROM review;
      review_time       
------------------------
 2017-08-20 08:00:02+08
 2017-07-27 00:45:33+08
 2017-07-27 00:45:37+08
 2017-07-28 02:24:03+08
 2017-07-28 02:24:27+08
 2017-07-28 02:24:31+08
 2017-07-28 02:25:31+08

Best Answer

I also don't see the problem

Create sample data,

CREATE TABLE foo(ts) AS VALUES (
  now()::timestamp without time zone
);

Display it,

TABLE foo;
             ts             
----------------------------
 2017-09-30 14:25:24.954084
(1 row)

You can see in the above no tz in output. Now let's change to use a with time zone.

ALTER TABLE foo
  ALTER COLUMN ts
  SET DATA TYPE timestamp with time zone;

Here is the output, notice you have a tz of -05

TABLE foo;

              ts               
-------------------------------
 2017-09-30 14:25:24.954084-05
(1 row)

What's your result of SHOW TIME ZONE?

Interestingly though, I do see what you're talking about with,

# SELECT now()::timestamp with time zone::timestamp;
            now             
----------------------------
 2017-09-30 14:27:53.061616
(1 row)

However, we have to know what you're asking.

Related Question