patternsqlMinor
Postgresql update timestamp without timezone to have timezone
Viewed 0 times
postgresqlwithoutupdatetimezonetimestamphave
Problem
When I created the table, a Timestamp column without timezone was created. All the imported time is in
Selecting the existing data does not show the added timezone information
Do I need to run an
Update 1
The output that is missing timezone information is due to the application itself (SQLWorkbenchJ). Querying from
UTC. Now I want to add the timezone information explicitly, and I have altered the columnALTER 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:54Do 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 timezonemydb # 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+08Solution
I also don't see the problem
Create sample data,
Display it,
You can see in the above no tz in output. Now let's change to use a
Here is the output, notice you have a tz of -
What's your result of
Interestingly though, I do see what you're talking about with,
However, we have to know what you're asking.
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 -
05TABLE 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.
Code Snippets
CREATE TABLE foo(ts) AS VALUES (
now()::timestamp without time zone
);TABLE foo;
ts
----------------------------
2017-09-30 14:25:24.954084
(1 row)ALTER TABLE foo
ALTER COLUMN ts
SET DATA TYPE timestamp with time zone;TABLE foo;
ts
-------------------------------
2017-09-30 14:25:24.954084-05
(1 row)# SELECT now()::timestamp with time zone::timestamp;
now
----------------------------
2017-09-30 14:27:53.061616
(1 row)Context
StackExchange Database Administrators Q#187346, answer score: 4
Revisions (0)
No revisions yet.