3

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
  • 1
    hmmm, not having any issues with this dbfiddle; at top of dbfiddle page you can change between PG 8.4 - 10, and all show the same results (ie, tz is displayed after altering table); what are you using as a client to connect to the db ... wondering if there could be a caching and/or client-side display issue? as a test, what happens if you create a new table/column with timestamp+tz, add a couple rows, then select ... do you see the tz? – markp Sep 30 '17 at 19:17
  • 1
    @markp you're creating the table with type timestamp. That's a macro for timestamp with time zone. The op is creating his table with timestamp without time zone – Evan Carroll Sep 30 '17 at 19:21
  • 1
    @EvanCarroll, not sure I'm following you; go to that dbfiddle, change the create table to use timestamp without time zone and you get the same results ... no tz displayed w/ first select, tz displayed w/ second select (ie, after alter table) – markp Sep 30 '17 at 19:26
  • My bad! Turns out the app I am using (SQLWorkbenchJ) filter out the timezone info, even though it is there. I should have known better and check using psql first. Thanks for your help! – hanxue Sep 30 '17 at 19:39
  • 1
    yeah, nothing like a good ol' basic pass-through CLI; damn them GUIs! :-P – markp Sep 30 '17 at 19:44
2

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.

  • 1
    SHOW TIME ZONE returns Asia/Kuala_Lumpur. The problem is with the DB management app. Running SELECT from psql shows the timezone – hanxue Sep 30 '17 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.