Timestamp binding not working with JS Date

[peroxy]

Environment

Knex version: 1.0.4
Database + version: PostgreSQL 14.2
OS: Ubuntu 20.04

Bug

I am having issues with a Date binding that should translate to a timestamp field in the SQL query.

This is the Knex code I am trying to use:

async (env: string, timestamp: Date, bmsPubkey: PublicKey) => {
  await knex(`${LOAN_TABLE} as l`)
    .select<{ created_on: Date }[]>('l.created_on')
    .join(`${OWNER_TABLE} as o`, `o.id`, '=', `l.owner_id`)
    .join(`${CLUSTER_TABLE} as cl`, `cl.id`, '=', `o.cluster_id`)
    .where('cl.name', '=', env)
    .orderByRaw(`abs(extract(epoch from (l.created_on - timestamp ?)))`, [timestamp])
    .limit(1);
}

I also tried replacing it with double ?? and using toIsoString(), but nothing helps:

    .orderByRaw(`abs(extract(epoch from (l.created_on - timestamp ??)))`, [timestamp])
// and:
    .orderByRaw(`abs(extract(epoch from (l.created_on - timestamp ?)))`, [timestamp.toISOString()])

Getting this error:
select "l"."created_on" from "loan" as "l" inner join "owner" as "o" on "o"."id" = "l"."owner_id" inner join "cluster" as "cl" on "cl"."id" = "o"."cluster_id" where "cl"."name" = $1 order by abs(extract(epoch from (l.created_on - timestamp $2))) limit $3 - syntax error at or near "$2"

When looking at the actual generated query (with toString() and toSql().toNative()) it looks like this:

SELECT "l"."created_on"
FROM "loan" AS "l"
         INNER JOIN "owner" AS "o" ON "o"."id" = "l"."owner_id"
         INNER JOIN "cluster" AS "cl" ON "cl"."id" = "o"."cluster_id"
WHERE "cl"."name" = 'devnet'
ORDER BY ABS(EXTRACT(EPOCH FROM (l.created_on - timestamp '2020-01-01T00:00:00.000Z')))
LIMIT 1

{
  sql: 'select "l"."created_on" from "loan" as "l" inner join "owner" as "o" on "o"."id" = "l"."owner_id" inner join "cluster" as "cl" on "cl"."id" = "o"."cluster_id" where "cl"."name" = $1 order by abs(extract(epoch from (l.created_on - timestamp $2))) limit $3',
  bindings: [ 'devnet', '2020-01-01T00:00:00.000Z', 1 ]
}

This works perfectly fine if I run it straight from the SQL console.

Stack trace:

{
   "code":"42601",
   "file":"scan.l",
   "length":92,
   "line":"1176",
   "name":"error",
   "position":"241",
   "routine":"scanner_yyerror",
   "severity":"ERROR",
   "stack":"error: select \"l\".\"created_on\" from \"loan\" as \"l\" inner join \"owner\" as \"o\" on \"o\".\"id\" = \"l\".\"owner_id\" inner join \"cluster\" as \"cl\" on \"cl\".\"id\" = \"o\".\"cluster_id\" where \"cl\".\"name\" = $1 order by abs(extract(epoch from (l.created_on - timestamp $2))) limit $3 - syntax error at or near \"$2\"\n    at Parser.parseErrorMessage (/home/dev/hubble-public-api/node_modules/pg-protocol/src/parser.ts:369:69)\n    at Parser.handlePacket (/home/dev/hubble-public-api/node_modules/pg-protocol/src/parser.ts:188:21)\n    at Parser.parse (/home/dev/hubble-public-api/node_modules/pg-protocol/src/parser.ts:103:30)\n    at Socket.<anonymous> (/home/dev/hubble-public-api/node_modules/pg-protocol/src/index.ts:7:48)\n    at Socket.emit (node:events:520:28)\n    at Socket.emit (node:domain:475:12)\n    at addChunk (node:internal/streams/readable:324:12)\n    at readableAddChunk (node:internal/streams/readable:297:9)\n    at Socket.Readable.push (node:internal/streams/readable:234:10)\n    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)",
   "timestamp":"2022-11-22 10:07:12"
}

[peroxy]

For now, I've fixed the issue by changing the query to use the to_timestamp function and using epoch number instead of the Date object binding:

.orderByRaw(`abs(extract(epoch from (l.created_on - to_timestamp(?))))`, [Math.floor(timestamp.valueOf() / 1000)])

[biggytech]

I'm having the same issue when binding timestamp with knex raw (startDate is '2023-02-21 02:28:00' JS string):

1. "date" BETWEEN TIMESTAMP :startDate AND ... <- syntax error
2. "date" BETWEEN TIMESTAMP '2023-02-21 02:28:00' AND .. <- no error
3. "date" BETWEEN :startDate AND .. <- no error

It works fine if I set js string inside of the query without binding. But for some reason it doesn't work with bound parameter.