Doctrine's date field - how to write query

Question

I'm using Symfony2 and Doctrine.

I have a date field, here it is:

/**
  * @ORM\Column(type="date")
  */
 protected $date;

In my form I use a text field to avoid Chrome default datepicker, but I insert DateTime objects in the database:

if ($request->isMethod('POST')) {
        $form->bind($request);

        //Convert string date to DateTime object and send it to database as object
        $dateObj = \DateTime::createfromformat('d-m-Y', $expense->getDate());
        $expense->setDate($dateObj);
        // ...

and then I want to find all items with a specific date:

public function findExpensesForDate($user, $date)
{
    $q = $this
        ->createQueryBuilder('e')
        ->where('e.date = :date')
        ->andWhere('e.user = :user')
        ->setParameter('date', $date)
        ->setParameter('user', $user)
         ->getQuery();

    return $q->getResult();
}

and call it like this:

$expenses_for_today = $this->repository->findExpensesForDate($this->user, $today);

which returns nothing when

$today = new /DateTime();

and returns the results when

$today_obj = new /DateTime();
$today = $today_obj->format('Y-m-d');

So why when I give the date as object this doesn't work? Isn't the reason to use date filed is to take advantage of quering with DateTime objects? I guess I'm missing something trivial and important, but I just can't see what, or I'm not understanding the situation quite well. My understanding is like this: the field is of type date, so I should insert DateTime objects in it and when quering I should also you DateTime objects. Can you please help me to fix this?

P.S.: I tried changing the field to datetime:

 /**
  * @ORM\Column(type="datetime")
  */
 protected $date;

but there was no change.

And at all is it OK and good to query with string? Will I get the advantage of using objects when querying that way?

Sybio · Accepted Answer · 2014-01-08T10:17:48.290

I think this is because a DateTime object is too much specific and have also hours, minutes and seconds, so it can't be equal to a registered date in database !

If you want to use DateTime objects, you need to get the date of the beginning of the day and the end date to get all results of the day ! You must compare an interval of dates to get all dates between it.

First, get the start and the end dates of the current day (to simplify, we will base the end date on the beginning of the next day, and we will exclude it in the request) :

$fromDate = new \DateTime('now'); // Have for example 2013-06-10 09:53:21
$fromDate->setTime(0, 0, 0); // Modify to 2013-06-10 00:00:00, beginning of the day

$toDate = clone $fromDate;
$toDate->modify('+1 day'); // Have 2013-06-11 00:00:00

And modify your method :

public function findExpensesForDate($user, $fromDate, $toDate)
{
    $q = $this
        ->createQueryBuilder('e')
        ->where('e.date >= :fromDate')
        ->andWhere('e.date < :toDate')
        ->andWhere('e.user = :user')
        ->setParameter('fromDate', $fromDate)
        ->setParameter('toDate', $toDate)
        ->setParameter('user', $user)
         ->getQuery();

    return $q->getResult();
}

That's all, it should work, here the script :

$expenses_for_today = $this->repository->findExpensesForDate($this->user, $fromDate, $toDate);

So you will get all the dates between the 2013-06-10 00:00:00 and the 2013-06-11 00:00:00 (excluded), so the results of the day !

Actually, the problem was in the dates. Settint $toDate in that way: `$toDate = new \DateTime(); $toDate->modify('+1 day'); $toDate->setTime(0, 0, 0);` fixed the problem. Thank you very much! — Faery, Jun 14 '13 at 05:59

Marcel Burkhard · Answer 2 · 2015-01-14T14:19:01.867

As others have mentioned, thats due to the time in DateTime.

Yet why am I answering?

Because I want to make you aware of the $qb->expr()->between()function.

The following snippet is from my ReservationRepository where I need to find the reservations for a given date ($today). My reservations have both a dateFrom and a dateTo attribute.

    if(null !== $today) {
        $today0 = new \DateTime($today->format("Y-m-d"));
        $today23 = new \DateTime($today->format("Y-m-d"));

        $today0->setTime(0,0,0);
        $today23->setTime(23,59,59);

        $qb->where($qb->expr()->orX(
            $qb->expr()->orX(
                $qb->expr()->between('r.dateFrom', ':today0', ':today23'),
                $qb->expr()->between('r.dateTo', ':today0', ':today23')
            ),
            $qb->expr()->andX(
                $qb->expr()->lte('r.dateFrom', ':today23'),
                $qb->expr()->gte('r.dateTo', ':today0')
            )
        ));
        $qb->setParameter('today0', $today0);
        $qb->setParameter('today23', $today23);
    }

The function between() saves me two lines of code in this example, and I find it to be more readable.

Doctrine's date field - how to write query

2 Answers2

Linked