0

I have a raw query (postgresDb) with date input

    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} 
   AND "createdAt" <= ${endDateTime}) orders 
   on orders.id=tempt."orderId" group by paystat
 `;

enter image description here

startDateTime and endDateTime as passed as Date object.

enter image description here

Please refer console print of start datetime and enddatetime which I am using to filter the query.

The above doesn't return me any data. only an empty array.

Now if I modify the raw query with datetime string, it returns data as expected.

    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" >=  '2023-07-13T22:09:43.528Z'
   AND "createdAt" <= '2023-07-13T22:11:54.938Z') orders 
   on orders.id=tempt."orderId" group by paystat
 `;

enter image description here The above returns me result.

enter image description here

Now I am confused why the above query didnt' return any result but when passed it a string directly it worked. any help ...

Jay
  • 336
  • 1
  • 4
  • 15
  • do `toISOString()` on start date and end date as you pass them to postgrsql to make sure they are in a compatible format with postgrsql – eshirvana Jul 13 '23 at 23:57
  • When I add toISOString() , postgres is throwing an error. Raw query failed. Code: `42883`. Message: `db error: ERROR: operator does not exist: timestamp without time zone >= text HINT: No operator matches the given name and argument types. You might need to add explicit type casts.` at zr.handleRequestError (F:\hotel\node_modules\@prisma\client\runtime\library.js:122:8308) – Jay Jul 14 '23 at 04:03
  • that's a prisma command , don't pass the command to postgrsql , send the result of the command to postgsql – eshirvana Jul 14 '23 at 04:04
  • I first converted the date to toISOString() then passed in the raw query. still the same error. I can run this with just js Date passed but with no result , when I convert that to toISOstirng(), postgres is throwing error.. – Jay Jul 14 '23 at 09:34

0 Answers0