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 &#039;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.phalconphp.com/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.



3.0k

Hello

And with using raw sql ?

with binding you can use php date_add function too



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