We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

BUG? Model Query parse error with date_add

I'm trying to run this query in sql: select * from jobs where completed_at is null and scheduled_for <= date_add(now(), interval 2 hour)

It runs successfully in console. I'm using the find params: [conditions => completed_at is null and scheduled_for <= date_add(now(), interval 2 hour)] which I'm pretty sure is fine.

However, I'm getting a parse error in the \Mvc\Model\Query class: Syntax error unexpected token integer(2), near to 'hour.

I'm assuming this is a bug, but wanted to check I'm not doing something stupid beforehand!

I've also tried using the query builder, and the parsing of the DATE_ADD(now(), interval 2 hour) is certainly problematic

I'm assuming it's related to this bug from 2015? https://forum.phalcon.io/discussion/8919/how-can-i-use-some-mysql-builtin-function-in-phql

For future reference i had to fudge it by using STR_TO_DATETIME(:dte:, :format:) and bind a formatted datetime object and send the format string.



5.1k

Hello

And with using raw sql ?

with binding you can use php date_add function too



145.0k
Accepted
answer

INTERVAL is part of mysql syntax, not sql itself. You need to add mysql exteison. https://github.com/phalcon/incubator/blob/master/Library/Phalcon/Db/Dialect/MysqlExtended.php