Get datetime difference with doctrine

Hi I'm trying to give amount of days and get records between that day and now.

            $now = new \DateTime();
            $days = 14;
            $to = $now->sub(new \DateInterval('P'.$days.'D'));
            $qb = $this->createQueryBuilder('c')
            $qb->andWhere('c.createdDate BETWEEN :from AND :to')
                    ->setParameter('from', $now)
                    ->setParameter('to', $to);
            $qb->getQuery()->getResult();

in my db created_date column and have a record which contain 2018-12-12. But unfortunately query returns no value :(. It would be great help if someone can solve. And I'm using sub to get minus date.

Solution

Valid query is:

$from = new \DateTime('-14 days');
$to = (new \DateTime())->setTime(23, 59, 59);

$qb = $this->createQueryBuilder('c')
$qb->andWhere('c.createdDate BETWEEN :from AND :to')
    ->setParameter('from', $from)
    ->setParameter('to', $to);

$result = $qb->getQuery()->getResult();

The reason it didn't work for you, is because the \DateTime is a mutable type. By changing a copy, you also changed the previous date object:

$from = new \DateTime();

// below you mutate the $from object, then return its instance
$to = $from->sub(new \DateInterval('P10D'));
// effect is both $from and $to reference the same object in memory

var_dump(spl_object_hash($from) === spl_object_hash($to));
echo $from->format('Y-m-d') , '<br>';
echo $to->format('Y-m-d');

Will result in:

bool(true) 
2018-12-07
2018-12-07

You mapped the property createdDate as datetime in Doctrine. Personally I always use the datetime_immutable type. Instead of DateTime I get to work with DateTimeImmutable, which, compared to DateTime is immutable by design so I don't have to worry about any references:

$from = new \DateTimeImmutable();
$to = $from->sub(new \DateInterval('P10D'));

var_dump(spl_object_hash($from) === spl_object_hash($to));
echo $from->format('Y-m-d') , '<br>';
echo $to->format('Y-m-d');

Results in:

bool(false)
2018-12-17
2018-12-07