Postgresql

Postgresql 更新沒有時區的時間戳以獲得時區

  • September 24, 2020

當我創建表時,創建了一個沒有時區的 Timestamp 列。所有導入的時間都在UTC. 現在我想明確添加時區資訊,並且我已經更改了列

ALTER TABLE review ALTER COLUMN review_time TYPE TIMESTAMP WITH TIME ZONE USING review_time AT TIME ZONE 'UTC';

選擇現有數據不顯示添加的時區資訊

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

我是否需要對UPDATE現有數據執行語句,如果需要,語法是什麼?

更新 1

缺少時區資訊的輸出是由於應用程序本身 (SQLWorkbenchJ) 造成的。查詢 frompsql將顯示時區

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

我也沒有看到問題

創建樣本數據,

CREATE TABLE foo(ts) AS VALUES (
 now()::timestamp without time zone
);

顯示它,

TABLE foo;
            ts             
----------------------------
2017-09-30 14:25:24.954084
(1 row)

您可以在上面看到輸出中沒有 tz。現在讓我們更改為使用with time zone.

ALTER TABLE foo
 ALTER COLUMN ts
 SET DATA TYPE timestamp with time zone;

這是輸出,注意你有一個 tz -05

TABLE foo;

             ts               
-------------------------------
2017-09-30 14:25:24.954084-05
(1 row)

你的結果是SHOW TIME ZONE什麼?

有趣的是,我確實看到你在說什麼,

# SELECT now()::timestamp with time zone::timestamp;
           now             
----------------------------
2017-09-30 14:27:53.061616
(1 row)

但是,我們必須知道你在問什麼。

2020 年新的原生答案

在 PostgreSQL 中,如果你只想要目前的日期時間,通過呼叫CURRENT_TIMESTAMP() 沒有時區,以及秒欄位小數點後的秒欄位中的小數位?

(在 PostgreSQL v12.4 上測試)

然後使用這個:

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

如果您將列的數據類型定義為timestamp(而不是timestamptz),那麼您可以儲存沒有時區的時間戳,在這種情況下您不需要添加TIMESTAMP WITHOUT TIME ZONE

像這樣:

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

在上面的函式中,傳遞0以去除秒欄位中的小數位數。

引用自:https://dba.stackexchange.com/questions/187346