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?
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 ;)