PostgreSQL时间列如何支持超过6位的毫秒数存储？

PostgreSQL原生的timestamp（不带时区）和timestamptz（带时区）类型默认仅支持最多6位小数的秒精度（微秒级），无法直接兼容SQL Server datetime2的7位小数（100纳秒级）需求。以下是几种可行的解决方案：

方案1：基于timestamp9扩展适配使用 #

你提到的timestamp9扩展虽然主打带时区的时间存储，但可以通过时区转换满足无时区场景的需求：

1. 安装扩展（需提前编译并部署到PostgreSQL扩展目录）：

   CREATE EXTENSION IF NOT EXISTS timestamp9;
   

2. 创建表时使用timestamp9类型：

   CREATE TABLE test (
       id INT,
       createdat timestamp9
   );
   

3. 插入7位精度时间时，指定统一时区（比如UTC）：

   INSERT INTO test VALUES (1, '2023-08-09 15:44:30.3196369+00');
   

4. 查询时转换为无时区的显示格式：

   SELECT id, createdat AT TIME ZONE 'UTC' AS createdat_no_tz FROM test;
   

方案2：拆分存储时间主体与额外精度 #

将时间拆分为两部分存储，分别保存6位精度的时间和第7位的偏移值：

1. 创建表：

   CREATE TABLE test (
       id INT,
       createdat timestamp(6),
       ns_offset INT CHECK (ns_offset BETWEEN 0 AND 9)
   );
   

2. 插入数据时拆分原时间：
   例如对于'2023-08-09 15:44:30.3196369'，提取6位精度部分和第7位的9：

   INSERT INTO test VALUES (1, '2023-08-09 15:44:30.319636', 9);
   

3. 查询时拼接还原完整时间：

   SELECT 
       id,
       CONCAT(TO_CHAR(createdat, 'YYYY-MM-DD HH24:MI:SS.US'), ns_offset) AS full_createdat
   FROM test;
   

   注意：排序、时间运算需同时关联createdat和ns_offset两个字段。

方案3：用numeric类型存储完整时间戳 #

将时间转换为带7位小数的数值形式存储，保留全部精度：

1. 创建表：

   CREATE TABLE test (
       id INT,
       createdat_num NUMERIC(20,7)
   );
   

2. 插入数据时，将SQL Server的datetime2转换为从纪元时间（1970-01-01）开始的秒数（带7位小数）：
   示例时间对应的数值为1691588670.3196369，插入语句：

   INSERT INTO test VALUES (1, 1691588670.3196369);
   

3. 查询时还原为时间格式：

   SELECT 
       id,
       TO_TIMESTAMP(createdat_num) AS createdat_truncated,
       CONCAT(TO_CHAR(TO_TIMESTAMP(createdat_num), 'YYYY-MM-DD HH24:MI:SS.US'), 
              SUBSTRING(createdat_num::TEXT FROM '\.(\d{7})$')) AS full_createdat
   FROM test;
   

   注：TO_TIMESTAMP会自动截断到6位精度，需通过字符串拼接还原第7位。

方案4：自定义存储类型（进阶） #

如果以上方案都不符合需求，可以开发自定义PostgreSQL类型，底层用字节数组存储完整的7位精度时间，并编写配套的转换、运算函数。这种方式适合具备PostgreSQL扩展开发能力的场景。

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