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
5
  • 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-fuso 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-fuso 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-fuso 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
  • 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
0

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.

2
  • Why not simply use localtimestamp? – a_horse_with_no_name Sep 24 '20 at 9:22
  • @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) – Kaz Sep 24 '20 at 10:14

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.