Prisma原生查询PostgreSQL：UTC时间查询结果不符的解决方法

你遇到的核心问题是PostgreSQL的timestamp without time zone类型不存储时区信息，而Prisma在传递Date对象作为查询参数时，会根据客户端运行环境的时区将其转换为对应时间，而非直接使用UTC时间，导致和数据库中存储的UTC时间比较时出现偏差。

比如你的场景：

• 数据库中user_first_active_at存的是UTC时间2022-10-25 19:00:01
• 你预期的查询参数是UTC时间2022-10-26 05:00:00（当前UTC减2天）
• 但Prisma可能将currentDate转换为客户端时区的时间，再传递给数据库，导致数据库实际比较的时间比预期的UTC时间更早，因此匹配到了本不该选中的Row2。

方案1：修改数据库字段类型（推荐） #

将user_first_active_at改为timestamp with time zone（PostgreSQL中简称timestamptz），这个类型会自动存储UTC时间，并在查询时正确处理时区转换。

步骤： #

1. 执行SQL修改表结构：

ALTER TABLE users 
ALTER COLUMN user_first_active_at 
TYPE timestamp with time zone 
USING user_first_active_at AT TIME ZONE 'UTC';

（USING子句用于将原有的UTC时间转换为timestamptz类型）

2. 更新Prisma Schema对应的字段类型为DateTime（Prisma会自动映射timestamptz到DateTime）：

model User {
  id                  BigInt    @id @default(autoincrement())
  user_first_active_at DateTime?
  created_at          DateTime  @default(now())
  updated_at          DateTime
}

之后再执行原查询，Prisma会正确将Date对象转换为UTC时间与数据库中的值比较，结果会符合预期。

方案2：不修改字段，手动处理时区转换 #

如果无法修改表结构，可以通过以下两种方式手动确保时间比较的准确性：

方式A：将查询参数转换为UTC时间字符串 #

把currentDate转换为UTC时区的ISO字符串，直接传递给数据库：

// 将currentDate转换为UTC时间的ISO格式字符串（不带时区后缀）
const utcCurrentDate = new Date(currentDate.getTime() - currentDate.getTimezoneOffset() * 60000)
  .toISOString()
  .slice(0, 23); // 保留到毫秒级，匹配数据库的timestamp(3)

const users = this.prisma.$queryRaw<User[]>`
  SELECT id::INTEGER 
  FROM users 
  WHERE user_first_active_at >= ${utcCurrentDate}::timestamp without time zone
`;

方式B：使用PostgreSQL的AT TIME ZONE函数转换字段 #

在查询中把数据库里的user_first_active_at（存储的UTC时间）转换为带时区的时间，再与参数比较：

const users = this.prisma.$queryRaw<User[]>`
  SELECT id::INTEGER 
  FROM users 
  WHERE (user_first_active_at AT TIME ZONE 'UTC') >= ${currentDate}
`;

这里user_first_active_at AT TIME ZONE 'UTC'会将原timestamp without time zone类型的值转换为timestamptz类型的UTC时间，与Prisma传递的Date对象（自动转为timestamptz）比较时会保持时区一致。

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