Repository navigation
Using NOW() in custom where? #56
Description
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`' }
Activity
mswdev commentedon May 27, 2019
@kylefarris
kylefarris commentedon Jun 28, 2019
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 commentedon Sep 20, 2020
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
.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