Postgresql

在 PostgreSQL 中儲存時區 HH:MM

  • December 18, 2021

我的應用程序需要在使用者選擇的時間向他們發送每日電子郵件,時區也由使用者自己定義。

儲存 HH:MM + 時區的最佳方式是什麼?知道我的伺服器位於特定時區,我如何有效地查詢這個?

我現在的表是:

CREATE TABLE "reminder" (
   "id"            SERIAL          UNIQUE PRIMARY KEY,
   "user"          INT             NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
   "scheduled_at   TIMESTAMPTZ     NOT NULL,
   "created_at"    TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP
)

我不確定是否TIMESTAMPTZ是最有效的方法,因為它儲存整個時間戳,而不僅僅是HH:MM

將 a**time時區名稱儲存為text**。

Not timetz,這已被破壞和阻止,而不僅僅是時間偏移,它會因夏令時 (DST) 和其他時區異常而中斷。

我建議:

CREATE TABLE reminder (
 id           int          GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, user_id      int          NOT NULL REFERENCES "user"(id) ON DELETE CASCADE
, scheduled_at time         NOT NULL
, tz           text         NOT NULL DEFAULT 'UTC'
, created_at   timestamptz  NOT NULL DEFAULT now()
);

要為“今天”生成正確的時間戳(由您目前的TimeZone設置定義):

SELECT *
    , (CURRENT_DATE + scheduled_at) AT TIME ZONE tz AS tstz
    , (CURRENT_DATE + scheduled_at) AT TIME ZONE tz AT TIME ZONE 'UTC' AS utc_ts
FROM   reminder;

結果tstz可能是接收者的不同“日子”。

utc_ts是 UTC 時區中的相應時間戳。

這個關於 SO 的相關答案非常適合併且有充分的解釋:

另外:我不會使用保留字“使用者”作為標識符。

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