Prisma原生查询传JS Date对象无结果，传日期字符串正常

问题现象 #

使用Prisma对PostgreSQL执行原生查询时，传入JS Date类型的startDateTime和endDateTime作为createdAt字段的筛选条件，查询返回空数组；但将参数替换为对应的ISO日期字符串后，查询能正常返回预期结果。

核心原因 #

1. 原生查询的参数序列化差异：Prisma的ORM查询（如findMany）会自动处理JS Date对象到PostgreSQL时间类型的转换，但$queryRaw原生查询的参数逻辑不同——直接传递Date对象时，Prisma可能将其序列化为带本地时区标识的非标准字符串（如Wed Jul 12 2023 15:09:43 GMT+0800 (中国标准时间)），这种格式PostgreSQL无法正确解析为timestamp with time zone类型，导致筛选条件完全不匹配，返回空数组。
2. ISO字符串的兼容性：手动传入的ISO格式字符串（如'2023-07-13T22:09:43.528Z'）是PostgreSQL原生支持的UTC时间格式，能和数据库中存储的createdAt（通常为timestamp with time zone类型）值精准匹配，因此查询正常返回结果。

解决方案 #

方案1：显式转换为ISO字符串（简单直接） #

在传入参数前，调用Date.prototype.toISOString()方法将Date对象转换为标准UTC格式的字符串，确保格式与数据库存储的时间一致：

const startDateTimeStr = startDateTime.toISOString();
const endDateTimeStr = endDateTime.toISOString();

const orderStatArr: any = await this.prismaService
  .$queryRaw`select sum(tempt.totalAmount), count(tempt."orderId"), orders."paystat" as paystat from 
  (select sum(items.count * items.amount) as totalAmount, items."orderId" from public."orderItem" items  group by items."orderId" ) tempt
  inner join
  (select id, "paymentStatus" as paystat from public."order" 
   where "createdAt" >=  ${startDateTimeStr} 
   AND "createdAt" <= ${endDateTimeStr}) orders 
   on orders.id=tempt."orderId" group by paystat
 `;

方案2：使用Prisma类型绑定（更安全规范） #

利用Prisma提供的Prisma.DateTime类型显式指定参数类型，强制Prisma将Date对象序列化为PostgreSQL兼容的时间格式：

import { Prisma } from '@prisma/client';

const orderStatArr: any = await this.prismaService
  .$queryRaw`select sum(tempt.totalAmount), count(tempt."orderId"), orders."paystat" as paystat from 
  (select sum(items.count * items.amount) as totalAmount, items."orderId" from public."orderItem" items  group by items."orderId" ) tempt
  inner join
  (select id, "paymentStatus" as paystat from public."order" 
   where "createdAt" >=  ${Prisma.DateTime(startDateTime)} 
   AND "createdAt" <= ${Prisma.DateTime(endDateTime)}) orders 
   on orders.id=tempt."orderId" group by paystat
 `;

也可以通过SQL类型转换语法，直接在查询中指定参数的数据库类型：

const orderStatArr: any = await this.prismaService
  .$queryRaw`select sum(tempt.totalAmount), count(tempt."orderId"), orders."paystat" as paystat from 
  (select sum(items.count * items.amount) as totalAmount, items."orderId" from public."orderItem" items  group by items."orderId" ) tempt
  inner join
  (select id, "paymentStatus" as paystat from public."order" 
   where "createdAt" >=  ${startDateTime}::timestamp with time zone 
   AND "createdAt" <= ${endDateTime}::timestamp with time zone) orders 
   on orders.id=tempt."orderId" group by paystat
 `;

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