Issue
consider an ISO8601 date and want to do a query using Typeorm and PostgreSQL
if (orderInput.begining && orderInput.ending)
query.andWhere(`order.createdAt
BETWEEN to_timestamp(${orderInput.begining} / 1000 )
AND to_timestamp(${orderInput.ending} / 1000 );
`);
These are my argument:
"2010-12-24T21:32:33.477Z"
"2019-12-24T21:32:33.477Z"
and this is the underhood query and the err :
query failed: SELECT DISTINCT "distinctAlias"."order_id" as "ids_order_id" FROM (SELECT "order"."id" AS "order_id", "order"."createdAt" AS "order_createdAt"
FROM "order" "order" INNER JOIN "ware" "ware" ON "ware"."id"="order"."wareId" WHERE "order"."organizationId" = $1 AND "order"."createdAt"
BETWEEN Wed Dec 25 2019 01:02:33 GMT+0330 (Iran Standard Time)
AND Wed Dec 25 2019 01:02:33 GMT+0330 (Iran Standard Time) ;) "distinctAlias" ORDER BY "order_id" ASC LIMIT 25 -- PARAMETERS: ["8fd87ced-eb58-4460-b74e-d5a2b1491622"]
error: { error: syntax error at or near "Dec"
I guess it's because of that the arguments are not taken wraped in ' ' (qoutes)
I don't know how to pass the arguments to typescript as standard ISO8606 Date
(typescript) with ' '(qoutes) to be then passed to PostgreSQL
Solution
Both Typescript And PostgreSQL know ISO8601 well and there is no need to to_timestamp() for PostgreSQL. So this works like a chram:
if (orderInput.begining && orderInput.ending)
query.andWhere(
`"order"."createdAt"
BETWEEN :begin
AND :end`
,{ begin: orderInput.begining, end: orderInput.ending);
NOTE: in JavaScript, you can easily make a ISO8601 standard Date in this way:
const date = new Date(2020,2,2)
const iso = date.toISOString()
Answered By - Amir Meyari
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.