Edit Question - KnowledgeHub

Title Body

I have an entity named Auction with two fields of type datetime: date1 and date2. I would like to SELECT all Auction rows where the difference between date1 and date2 is less then 30 minutes. How can I do this using Doctrine Query Language? DATE_DIFF(date1, date2) as documentation says "Calculate the difference in days between date1-date2". Where I need the difference to the minute. EDIT : So here is the complete implementation of a custom function TIME_DIFF : use Doctrine\ORM\Query\Lexer; use Doctrine\ORM\Query\AST\Functions\FunctionNode; /** * Custom DQL function returning the difference between two DateTime values * * usage TIME_DIFF(dateTime1, dateTime2) */ class TimeDiff extends FunctionNode { /** * @var string */ public $dateTime1; /** * @var string */ public $dateTime2; public function parse(\Doctrine\ORM\Query\Parser $parser) { $parser->match(Lexer::T_IDENTIFIER); $parser->match(Lexer::T_OPEN_PARENTHESIS); $this->dateTime1 = $parser->ArithmeticPrimary(); $parser->match(Lexer::T_COMMA); $this->dateTime2 = $parser->ArithmeticPrimary(); $parser->match(Lexer::T_CLOSE_PARENTHESIS); } public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { return 'TIME_TO_SEC(TIMEDIFF(' . $this->dateTime1->dispatch($sqlWalker) . ', ' . $this->dateTime2->dispatch($sqlWalker) . '))'; } } Then in your app/config/ config.yml add this: doctrine: (...) orm: (...) dql: numeric_functions: time_diff: MyProject\DQL\TimeDiff

Tags (comma-separated) Cancel