Doctrine 2 - Filter Results by Date Date

Question

I have a doctrine object called Szerzodes with a datetime field called exportalva . Is it possible to somehow get records for which the exportalva date exportalva is a given value? For example, I want to get records that were exported on a specific day, regardless of time. I tried

 $em->createQuery("SELECT sz FROM Szerzodes sz WHERE DATE(sz.exportalva) = '2012-05-17'") 

but this failed because DATE () is not a known function for DQL. Now I am temporarily using

 $em->createQuery("SELECT sz FROM Szerzodes sz WHERE sz.exportalva LIKE '2012-05-17%'") 

but that seems like an ugly hack to me. Is there a better way to do this?

Answer 1

Given that you have a DATETIME field in your database, it is likely that using LIKE (it depends entirely on the database and table mechanism) you are not using algorithms optimized for DATE operations.

One solution is to specify the whole day (from the first to the last second) using the BETWEEN operator, which is supported by DQL , as shown in it EBNF:

 $em->createQuery("SELECT sz FROM Szerzodes sz WHERE sz.exportalva BETWEEN '2012-05-17 00:00:00' AND '2012-05-17 23:59:59'") 

If you really want to use the function for this, it is also useful to use the function always in the value, and not in the column used in this state. Some databases (e.g. MySQL) do not use indexes on columns modified with built-in functions. You can create a user-defined function called DATE that does what you expect (this can easily complete the previous example above) using the DQL User-defined function .