DATE_ADD() is not working in MySQL XDev API with Node.js

Question

In MySQL shell I am able to run

select * from meeting where startTime > date_add(now(), interval 2 day);

This same command is not working using MySQL XDev API

var meetingRows = (await sqldb.getTable('meeting').select().where('startTime > DATE_ADD(NOW(), interval 2 DAY)').execute()).fetchAll()

I am getting error

-- PARSING FAILED --------------------------------------------------

> 1 | DATE_ADD(NOW(), interval 2 DAY)
    |                          ^

Expected one of the following: 

'!=', '%', '&&', '&', '*', '+', ',', '-', '/', '<', '<<', '<=', '<>', '=', '==', '>', '>=', '>>', '^', 'and', 'between', 'div', 'in', 'is', 'like', 'not', 'or', 'overlaps', 'regexp', '|', '||'

    at _.tryParse (/usr/src/app/node_modules/parsimmon/src/parsimmon.js:928:15)
    at Object.parse (/usr/src/app/node_modules/@mysql/xdevapi/lib/ExprParser/index.js:46:27)
    at Object.getValue (/usr/src/app/node_modules/@mysql/xdevapi/lib/DevAPI/ProjectedSearchExprStr.js:88:27)
    at /usr/src/app/node_modules/@mysql/xdevapi/lib/DevAPI/Table.js:276:95
    at Array.map (<anonymous>)
    at Object.select (/usr/src/app/node_modules/@mysql/xdevapi/lib/DevAPI/Table.js:276:18)

What is wrong in my code? How can I make this working?

backtick is just to create string. I have changed backtick to single quote — Alok, Jul 07 '23 at 18:20

score 0 · Answer 1 · answered Jul 07 '23 at 19:29

const mysqlx = require('@mysql/xdevapi');

const session = mysqlx.getSession({
  user: 'your_user',
  password: 'your_password',
  host: 'localhost',
  port: 33060,
  schema: 'your_schema'
});

const meetingTable = session.getSchema('your_schema').getTable('meeting');
const now = mysqlx.expr('NOW()');
const interval = mysqlx.expr('INTERVAL 2 DAY');

const meetingRows = await meetingTable
  .select()
  .where(mysqlx.expr('startTime > :startTime', { startTime: now.add(interval) }))
  .execute()
  .fetchAll();

console.log(meetingRows);

I am getting error `now.add is not a function`. Is it working for you? — Alok, Jul 08 '23 at 08:15

score 0 · Answer 2 · answered Jul 10 '23 at 13:55

In theory, with an X DevAPI expression, you should be able to use a simplified flavour of that SQL statement:

NOW() + interval 2 DAY
CURDATE() + interval 2 DAY

So, you can use the API like the following:

.where('startTime > NOW() + interval 2 DAY')

In practice, I think it's not really working, so you can still use the regular SQL with session.sql().

I suggest you report a bug at https://bugs.mysql.com/ using the Connector for Node.js category.

Disclaimer: I'm the lead developer of the MySQL X DevAPI connector for Node.js

DATE_ADD() is not working in MySQL XDev API with Node.js

2 Answers2