Error when using to_timestamp in node-postgres insert statement

Question

I am using node-postgres and attempting to use to_timestamp and receive the error:

ReferenceError: to_timestamp is not defined

Here is my code:

let sDate = format(parseISO(myStartDate),'dd/MM/yyyy')
let sTime = format(parseISO(myStartTime), 'HH:mm:ss')
let sDateTime = `${sDate} ${sTime}`
let eDate = format(parseISO(myEndDate),'dd/MM/yyyy')
let eTime = format(parseISO(MyEndTime), 'HH:mm:ss')
let eDateTime = `${eDate} ${eTime}`

console.log(sDate, sTime, sDateTime)
console.log(eDate, eTime, eDateTime)

data = await pool.query(        
      "INSERT INTO my_table (window_start, window_end ) VALUES($1, $2) RETURNING data_id",
          [ to_timestamp(sDateTime,'YYYY-MM-DD HH24:MI:SS'), to_timestamp(eDateTime,'YYYY-MM-DD HH24:MI:SS') ]
    );

Here is my console output:

25/05/2021 19:56:40 25/05/2021 19:56:40
25/05/2021 19:56:40 25/05/2021 19:56:40

but then errors with ReferenceError: to_timestamp is not defined

Please note that my table columns for both window_start and window_end are of type timestamp

Any ideas please?

Answer 1

The parameters are not interpolated into the SQL statement, but the to_timestamp function is executed in Javascript, which causes the error.

Use something like

data = await pool.query(        
      "INSERT INTO my_table (window_start, window_end) VALUES (to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS'), to_timestamp($2, 'YYYY-MM-DD HH24:MI:SS') RETURNING data_id",
          [ sDateTime, eDateTime ]
    );