主页 / dba / 问题 / 309964
Accepted
Ουιλιαμ Αρκευα
Asked: 2022-03-21 17:51:24 +0800 CST

从函数返回带有时区的时间戳时丢失时区

  • 772

我有这个功能：

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

为什么时区信息会丢失？

postgresql functions

1 个回答

  1. Best Answer

    数据类型timestamp with time zone( timestamptz)不存储任何时区信息。这是一个常见的误解，受误导性名称的启发。（责怪 SQL 标准委员会！）你不是第一个为此而堕落的人：

    时间偏移只是一个输入修饰符/输出修饰符。Postgres 总是在内部存储 UTC 时间。这里的基础：

    考虑这个演示：

    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 是如何变化的。看：

    这也说明了为什么您当前的功能本质上是不可靠的。一个普通的 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：

    我还替换了您的混合大小写标识符。看：

    确定性函数

    要获得确定性结果，还要定义时区。喜欢

    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第二个参数。适应您的需求。关于参数默认值：

    您只需添加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 和其他奇怪的东西一起正常工作。

    在 中查找可用时区名称pg_catalog.pg_timezone_names。

    • 3

相关问题