Using NOW() in custom where?

mswdev commented 5 years ago

I need to select data where `last_update >= now() - INTERVAL 1 MINUTE`

Example of what I'm currently trying to use:

qb.select('*').where('last_update >= NOW() - INTERVAL 1 MINUTE', null, false).get('account', (err, rows) => {
            qb.release();
            if (err) throw err;
            return res.json(rows)
        })

EDIT: It looks like the issue seems to be occurring due to it placing NOW() inside single quotes when executing the query causing mysql to treat it as a field rather than a function. Any idea how to get around this?

EDIT 2: I realized you can pass false for escape strings which is what I want for executing the NOW() function, but for some reason, it keeps appending the AS keyword after the function? Any idea why this is?

I'm using the query above, but it seems to return this with the AS keyword which is clearly not right?:

ComQueryPacket {
  command: 3,
  sql:
   'SELECT * FROM `account` WHERE last_update >= NOW() AS `- INTERVAL 1 MINUTE`' }

mswdev commented 5 years ago

@kylefarris

kylefarris commented 5 years ago

Hey @Sphiinx, off the top of my head, I'm not sure why it would do that but surely it can be solved. I've been really busy trying to meet a deadline on a big project due mid-July so I haven't been able to answer questions for my open-source projects. I'd be more than happy to accept a PR with the solution, though, as long as it includes a test to go along with it!

rohit-gh commented 4 years ago

Hi @mswdev @kylefarris , I know I'm late here. Just ran into the same situation Although I was not able to find a substitute for Now()

I found a workaround using https://momentjs.com/ library as mentioned below

const moment = require('moment');

qb.select('*').where(`last_update >= '${moment().utc().format('YYYY-MM-DD HH:mm:ss')}' - INTERVAL 1 MINUTE`, null, false).get('account', (err, rows) => {
            qb.release();
            if (err) throw err;
            return res.json(rows)
        })

.format('YYYY-MM-DD HH:mm:ss')} is important here as this is the format of the timestamp that is being saved in DB and used for querying.

also note I added .utc() , please change this if you have a different configured timezone

kylefarris / node-querybuilder

Using NOW() in custom where? #56