從函式返回帶有時區的時間戳時失去時區

我有這個功能：

CREATE OR REPLACE FUNCTION laborable_day(dtDateTime TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
 _isHoliday BOOLEAN;
BEGIN
 LOOP
   SELECT COUNT(*) > 0 INTO _isHoliday 
   FROM holidays 
   WHERE holiday = DATE(dtDateTime);
   
   IF _isHoliday THEN
     dtDateTime := dtDateTime + INTERVAL '1 DAY';
   ELSE
     EXIT;
   END IF;
 END LOOP;
 RETURN dtDateTime;
END;
$$ LANGUAGE plpgsql STRICT SECURITY DEFINER;

如果我執行這個命令：

select laborable_day('2022-01-01 18:53:11.14297-05'::TIMESTAMPTZ);

我得到：

+------------------------------+
| laborable_day                |
|------------------------------|
| 2022-01-02 23:53:11.14297+00 |
+------------------------------+
SELECT 1
Time: 0.012s

為什麼時區資訊會失去？

數據類型timestamp with time zone( timestamptz)不儲存任何時區資訊。這是一個常見的誤解，受誤導性名稱的啟發。（責怪 SQL 標準委員會！）你不是第一個為此而墮落的人：

• 數據類型“帶時區的時間戳”中的時區儲存

時間偏移只是一個輸入修飾符/輸出修飾符。Postgres 總是在內部儲存 UTC 時間。這裡的基礎：

• 在 Rails 和 PostgreSQL 中完全忽略時區

考慮這個展示：

test=> SET timezone = 'UTC';
SET
test=> SELECT timestamptz '2022-01-01 18:53:11.14297-05';
        timestamptz          
------------------------------
2022-01-01 23:53:11.14297+00
(1 row)

test=> SET timezone = 'America/New_York';
SET

test=> SELECT timestamptz '2022-01-01 18:53:11.14297-05';
        timestamptz          
------------------------------
2022-01-01 18:53:11.14297-05

test=> SELECT timestamptz '2022-03-21 18:53:11.14297-05';
        timestamptz          
------------------------------
2022-03-21 19:53:11.14297-04
(1 row)

還要注意最後一次通話的 DST 是如何變化的。看：

• 帶有 AT TIME ZONE 和 UTC 偏移量的怪事

這也說明了為什麼您目前的功能本質上是不可靠的。一個普通的 from timestamptztodate假定你的會話的目前時區設置。如果沒有給出它適用的時區，日期就沒有明確定義。如果您對模糊定義感到滿意，請考慮這個改進的（但仍然很幼稚）的功能：

CREATE OR REPLACE FUNCTION laborable_day(INOUT _dt timestamptz)
 LANGUAGE plpgsql STRICT AS  -- why SECURITY DEFINER ?
$func$
BEGIN
  LOOP
     IF EXISTS (
        SELECT FROM holidays
        WHERE  holiday = _dt::date  -- depends on current time zone! 
        ) THEN
        _dt := _dt + interval '1 day';
     ELSE
        EXIT;
     END IF;
 END LOOP;
END
$func$;

我刪除了SECURITY DEFINER. 僅在必要時使用它，因為它具有潛在危險。相反，SELECT將您的holidays表授予PUBLIC.

為簡單起見使用INOUT參數。

關於EXISTS：

• PL/pgSQL 檢查一行是否存在

我還替換了您的混合大小寫標識符。看：

• PostgreSQL 列名是否區分大小寫？

確定性函式

要獲得確定性結果，還要定義時區。像

CREATE OR REPLACE FUNCTION laborable_day(INOUT _dt timestamptz, _tz text DEFAULT 'UTC')  -- default UTC?
 LANGUAGE plpgsql STRICT AS  -- why SECURITY DEFINER ?
$func$
DECLARE
  _day date := (_dt AT TIME ZONE _tz)::date;
  _inc int := 0;
BEGIN
  LOOP
     IF EXISTS (
        SELECT FROM holidays
        WHERE  holiday = _day + _inc
        ) THEN
        _inc := _inc + 1;
     ELSE
        EXIT;
     END IF;
 END LOOP;
 
 _dt := _dt + interval '1 day' * _inc;
END
$func$;

我添加了“UTC”作為DEFAULT第二個參數。適應您的需求。關於參數預設值：

• PL/pgSQL 函式中的可選參數

您只需添加integer到date.

我建議不要“重載”函式（創建具有不同函式參數的變體），這可能會很棘手。

這樣你仍然可以呼叫只給出一個的函式timestamptz：

SELECT laborable_day('2022-01-01 18:53:11.14297-05');

要獲得給定時區的結果，即“歐洲/維也納”：

SELECT laborable_day('2022-01-01 18:53:11.14297-05', 'Europe/Vienna');

使用時區名稱，而不是縮寫或數字偏移量，使其與 DST 和其他奇怪的東西一起正常工作。

• 帶有 AT TIME ZONE 和 UTC 偏移量的怪事

在 中查找可用時區名稱pg_catalog.pg_timezone_names。

引用自：https://dba.stackexchange.com/questions/309964