Postgresql update timestamp without timezone to have timezone

Question

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

• markp-fuso: 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?
• Evan Carroll: @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
• markp-fuso: @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)
• hanxue: 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!
• markp-fuso: yeah, nothing like a good ol' basic pass-through CLI; damn them GUIs! :-P

Answer 1

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.

• hanxue: SHOW TIME ZONE returns Asia/Kuala_Lumpur. The problem is with the DB management app. Running SELECT from psql shows the timezone

Answer 2

New, and native answer in 2020

In PostgreSQL, If you only want the current date-time by calling CURRENT_TIMESTAMP() without time zone, and fractional digits in the seconds field which come after the decimal point of the seconds field?

(Tested on PostgreSQL v12.4)

Then use this:

SELECT CURRENT_TIMESTAMP(0)::TIMESTAMP WITHOUT TIME ZONE;

If you define your column's data type as timestamp (not as timestamptz), then you can store the timestamp without time zone, in that case you don't neet to add TIMESTAMP WITHOUT TIME ZONE

Like this:

CREATE TABLE foo (created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(0))

In the above function, 0 is passed to get rid of the fractional digits in the seconds field.

• user1822: Why not simply use localtimestamp?
• Kaz: @a_horse_with_no_name LOCALTIMESTAMP also acceptable, but just calling LOCALTIMESTAMP without passing any argument for it the output of that function will also bring fractional digits in the seconds field, and to get rid of the fractional digits in the seconds field, can call this function by passing 0 as an argument LOCALTIMESTAMP(0)

Answer 3

One option I use to update a numeric column to be datetime

ALTER TABLE table ALTER COLUMN field TYPE TIMESTAMP WITHOUT TIME ZONE USING NULL::timestamp without time zone;