We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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.0k

Hello

And with using raw sql ?

with binding you can use php date_add function too



144.9k
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