How to get date difference in Doctrine Query Language (dql)?

Question

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    

Hackneyed

Answer 1

You can use MySQL's TIMEDIFF (with TIME_TO_SEC and divide by 60) :

TIME_TO_SEC(TIMEDIFF(date1, date2)) / 60;

I believe you can implement it in Doctrine2 like in this example for DATE_DIFF.

Rogozen

I hoped there will be some built-in dql solution for this. But it seems that defining custom function will be necessary. Thanks. – Hackneyed

And here info how to register such custom function in Symfony2: symfony.com/doc/2.0/cookbook/doctrine/custom_dql_functions.html – Hackneyed