Postgres date_trunc(text, timestamp) with Doctrine 2 in Symfony?

Question

How to use Postgres date_trunc(text, timestamp) function with Doctrine 2 in Symfony?

Answer 1

1. In your project, create a new folder named DoctrineExtensions
2. In the new folder, create a file named DateTrunc.php
3. Paste this code:

DateTrunc.php

<?php
namespace YOUR_BUNDLE_HERE\DoctrineExtensions;

use Doctrine\ORM\Query\AST\Functions\FunctionNode,
    Doctrine\ORM\Query\Lexer;

/**
 * DateTrunc ::= "date_trunc" "(" ArithmeticPrimary "," ArithmeticPrimary ")"
 */
class DateTrunc 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 'date_trunc(' .
        $this->firstDateExpression->dispatch($sqlWalker) . ', ' .
        $this->secondDateExpression->dispatch($sqlWalker) .
    ')'; // (7)
  }
}

4. Add the function to Doctrine configuration

config.yml

orm:
    dql:
        datetime_functions:
             date_trunc: YOUR_BUNDLE_HERE\DoctrineExtensions\DateTrunc

5. You can now use date_trunc(text, timestamp) with Doctrine!

Note: You can easily adapt this code for every additional Postgres/MySQL function. Checkout DoctrineExtensions