使用node-postgres与原生SQL查询时时间戳不一致问题

环境与问题 #

• PostgreSQL运行在Docker容器中，NestJS服务从宿主机启动，数据库由另一微服务通过Prisma创建
• 本地时区为UTC+5，使用node-postgres执行以下插入语句：

  INSERT INTO "Chat" default values RETURNING *
  

• 问题：插入的created_at时间戳比本地时间晚10小时，预期应为本地时间减5小时（即UTC标准时间）
• 补充：执行select current_timestamp;返回2023-11-12 15:18:37.322506+00，本地时间为2023-11-12 20:18:37.322506+00

表结构定义 #

CREATE TABLE "Chat" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "Chat_pkey" PRIMARY KEY ("id")
);

解决方案 #

1. 确认Docker容器的PostgreSQL时区 #

PostgreSQL默认使用UTC时区，可通过以下步骤验证：

• 进入PostgreSQL容器：

  docker exec -it <容器ID> psql -U <用户名> -d <数据库名>
  

• 执行SQL查询时区：

  SHOW timezone;
  

• 若返回非UTC，需修改容器时区，启动容器时添加环境变量：

  docker run -d \
    -e POSTGRES_USER=<用户名> \
    -e POSTGRES_PASSWORD=<密码> \
    -e POSTGRES_DB=<数据库名> \
    -e TZ=UTC \
    postgres:latest
  

  或在docker-compose.yml中配置：

  services:
    postgres:
      image: postgres:latest
      environment:
        POSTGRES_USER: <用户名>
        POSTGRES_PASSWORD: <密码>
        POSTGRES_DB: <数据库名>
        TZ: UTC
  

2. 修改表字段为带时区的时间戳 #

将created_at字段类型改为TIMESTAMPTZ(3)，确保存储带时区的UTC时间，避免转换歧义：

ALTER TABLE "Chat" ALTER COLUMN "created_at" TYPE TIMESTAMPTZ(3);

3. 配置node-postgres连接时区 #

在node-postgres的连接配置中明确指定时区为UTC，避免客户端自动转换时间：

const { Pool } = require('pg');
const pool = new Pool({
  user: '<用户名>',
  host: 'localhost',
  database: '<数据库名>',
  password: '<密码>',
  port: 5432,
  timezone: 'UTC' // 强制使用UTC时区
});

4. 验证结果 #

• 执行select current_timestamp;应返回UTC时间（与本地时间差5小时）
• 插入数据后，created_at字段值应为UTC时间，即本地时间减5小时，符合预期

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