HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Postgresql update timestamp without timezone to have timezone

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlwithoutupdatetimezonetimestamphave

Problem

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

Solution

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.

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.