PostgreSQL中如何使用变量设置timestamptz类型的精度？

问题背景 #

尝试给timestamptz(p)的精度参数p使用变量而非常量，执行以下代码时报错：

DO $$
DECLARE precision int = 3;
BEGIN SELECT now()::timestamptz(precision);
END; $$;

错误信息：

ERROR:  invalid input syntax for type integer: "precision"
LINE 1: SELECT now()::timestamptz(precision)
                      ^

更换变量类型为int2/int4/int8仍无法解决，执行SELECT now()::timestamptz(3::int);得到明确提示：

ERROR:  type modifiers must be simple constants or identifiers
LINE 1: SELECT now()::timestamptz(3::int);
                      ^

需求是根据数据源动态调整精度：API来源日期设为3位，数据库自身日期保留6位，不想用条件分支硬编码常量。

常量设置的正常示例：

postgres=# SELECT now()::timestamptz(6);
              now               
-------------------------------
 2023-05-20 18:29:23.915378+00
(1 row)

postgres=# SELECT now()::timestamptz(3);
            now              
----------------------------
 2023-05-20 18:29:27.263+00
(1 row)

可行解决方案 #

1. 字符串格式化转换 #

通过to_char按指定精度格式化时间字符串，再转回timestamptz，支持任意0-6之间的精度值：

DO $$
DECLARE
  target_precision int = 3;
  time_str text;
  adjusted_time timestamptz;
BEGIN
  -- 生成对应精度的格式字符串
  time_str := to_char(now(), 'YYYY-MM-DD HH24:MI:SS.' || repeat('S', target_precision) || 'TZ');
  adjusted_time := to_timestamp(time_str, 'YYYY-MM-DD HH24:MI:SS.' || repeat('S', target_precision) || 'TZ');
  RAISE NOTICE '调整后时间：%', adjusted_time;
END; $$;

2. 动态SQL拼接 #

利用EXECUTE和format函数安全拼接SQL语句，绕开类型修饰符必须为常量的限制：

DO $$
DECLARE
  target_precision int = 3;
  adjusted_time timestamptz;
BEGIN
  EXECUTE format('SELECT now()::timestamptz(%L)', target_precision) INTO adjusted_time;
  RAISE NOTICE '调整后时间：%', adjusted_time;
END; $$;

注意：format函数的%L占位符会自动处理变量转义，避免SQL注入风险。

3. date_trunc截断（适配0/3/6精度场景） #

如果仅需要0（秒）、3（毫秒）、6（微秒）这几种精度，可通过date_trunc结合变量控制截断单位：

DO $$
DECLARE
  target_precision int = 3;
  trunc_unit text;
  adjusted_time timestamptz;
BEGIN
  trunc_unit := CASE target_precision
    WHEN 0 THEN 'second'
    WHEN 3 THEN 'millisecond'
    WHEN 6 THEN 'microsecond'
    ELSE 'microsecond' -- 默认 fallback 到微秒
  END;
  adjusted_time := date_trunc(trunc_unit, now());
  RAISE NOTICE '调整后时间：%', adjusted_time;
END; $$;

这种方法性能最优，适合你的需求场景。

内容的提问来源于stack exchange，提问作者jcalfee314