Search only by time in DateTime field in Symfony2 and Doctrine

Question

I use Symfony 2 and Doctrine. In my database MySQL, I have a field of type DateTime. In my Repository file, by using QueryBuilder, I would like to search in this table only by date (without time) and only by time (without date), in my Where clause.

How can I realize this?

Pourparler

you can use native query and then it bind to entity – Casebook

Answer 1

You can extend and register the DATE() function in doctrine2 to play with DQL. check this solution.

Now if you don't want extending doctrine2, here my solutions :

Search based on date :

You can get the interval between the datetime of beginning of a day and the datetime of its end, I already answer here.

This is the cleanest solution for the date (I think) !

Now another easy solution is to compare a datetime field as a string with LIKE operator, so you can also do that :

$query = $repository->createQueryBuilder('u')->where('u.createdAt LIKE :date')
        ->setParameter('date', '2013-05-06%')
        ->getQuery();

        $users = $query->getResult();

Search based on hour :

I don't know another solution with DQL than comparing as string (Unless you extend doctrine with DATE), here the code :

$query = $um->getRepository()->createQueryBuilder('u')->where('u.createdAt LIKE :date')
        ->setParameter('date', '____-__-__ 10:45:__')
        ->getQuery();

To remind you, "_" (underscore) means 1 character (and any character !).

Choose your solution ;)

Reeducate

Thank you very much for your answer, you're a genius, it work perfectly! Finally, I extend and register the DATE() and TIME() function. I've also tried your others solutions and all is OK! – Pourparler

Answer 2

Part of my code which works for me:

$qb->andWhere('s.createdAt LIKE :createdAt');
$qb->setParameter('createdAt', $startDate->format("Y-m-d") . "%");

Johnstone