DQL时差

Question

如何计算两个日期时间字段之间的时间差。这里我想找出ept.endedTime和ept.startedTime的时间差。

$query = $em->createQuery('SELECT t.code, t.name, t.description, ept.endedTime, ept.startedTime, DATE_DIFF(ept.endedTime, ept.startedTime)'
. ' FROM AdrotecCompanyBundle:EmployeeProjectTask ept'
. ' JOIN ept.task t'
. ' JOIN ept.employeeProject ep'
. ' JOIN ep.employee e'
. ' WHERE ep.project = :pId');

Answer 1

使用这编写用户定义的dql函数，然后使用它。

<?php
class DateDiff extends FunctionNode
{
    // (1)
    public $firstDateExpression = null;
    public $secondDateExpression = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER); // (2)
        $parser->match(Lexer::T_OPEN_PARENTHESIS); // (3)
        $this->firstDateExpression = $parser->ArithmeticPrimary(); // (4)
        $parser->match(Lexer::T_COMMA); // (5)
        $this->secondDateExpression = $parser->ArithmeticPrimary(); // (6)
        $parser->match(Lexer::T_CLOSE_PARENTHESIS); // (3)
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'DATEDIFF(' .
            $this->firstDateExpression->dispatch($sqlWalker) . ', ' .
            $this->secondDateExpression->dispatch($sqlWalker) .
        ')'; // (7)
    }
}