Compare dates between datetimes with Doctrine

Question

I have a Symfony2 application with a table that contains a date field, whose type is DateTime.
I need to get all the entities where that field value is now.

If I uses the following code, I get 0 results because Doctrine is comparing the DateTime object.

$now = new \DateTime();
data = $entityRepository->findByDate($now);

I need to only compare year, month, and day, not hours.

How can I achieve this?

goto · Accepted Answer · 2015-04-28T11:46:20.577

63

I see this simple way:

$now = new \DateTime();

$data = $entityRepository->getByDate($now);

then in your repository

public function getByDate(\Datetime $date)
{
    $from = new \DateTime($date->format("Y-m-d")." 00:00:00");
    $to   = new \DateTime($date->format("Y-m-d")." 23:59:59");

    $qb = $this->createQueryBuilder("e");
    $qb
        ->andWhere('e.date BETWEEN :from AND :to')
        ->setParameter('from', $from )
        ->setParameter('to', $to)
    ;
    $result = $qb->getQuery()->getResult();

    return $result;
}

edited Apr 28 '15 at 11:46

answered Apr 28 '15 at 11:37

goto

7,908
10
48
58

2

When you are ready to move to the next coding level, you guys could read this article about value object and time precision. http://rosstuck.com/precision-through-imprecision-improving-time-objects – goto Aug 11 '17 at 13:33
you can also use SQL DATE() function following the steps in this excellent answer to a similar question: https://stackoverflow.com/a/25999716/4954580 – Roubi Apr 12 '19 at 17:59

score 5 · Answer 2 · answered Jan 19 '16 at 08:40

Method in repository

public function getDays(\DateTime $firstDateTime, \DateTime $lastDateTime)
{
    $qb = $this->getEntityManager()->createQueryBuilder()
        ->select('c')
        ->from('ProjectBundle:Calendar', 'c')
        ->where('c.date BETWEEN :firstDate AND :lastDate')
        ->setParameter('firstDate', $firstDateTime)
        ->setParameter('lastDate', $lastDateTime)
    ;

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

    return $result;
}

And action

public function calendarAction()
{
    $currentMonthDateTime = new \DateTime();
    $firstDateTime = $currentMonthDateTime->modify('first day of this month');
    $currentMonthDateTime = new \DateTime();
    $lastDateTime = $currentMonthDateTime->modify('last day of this month');

    $days = $this->getDoctrine()
        ->getRepository('ProjectBundle:Calendar')
        ->getDays($firstDateTime, $lastDateTime);

    return ['days' => $days];
}

score 2 · Answer 3 · answered Apr 28 '15 at 11:32

There is a difference between the date and datetime types in doctrine.

date: Type that maps a SQL DATETIME to a PHP DateTime object.

datetime: Type that maps a SQL DATETIME/TIMESTAMP to a PHP DateTime object.

Make sure you have set the column type to date instead of datetime.

Alternatively - as a workaround - you could get the day from the original date1 and then search between a same-day date2 -> 00:00:00 and same-day date3 -> 23:59:59 using a custom repository method.

Compare dates between datetimes with Doctrine

3 Answers3

Linked