2

I have a doctrine entity called Szerzodes with a datetime field called exportalva. Is it possible somehow to get the records of which the exportalva field's date part is a given value? E.g I want to get the records that were exported on a given day, regardless of the time. I've tried to

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

but it failed as DATE() is not a known function for DQL. Now I temporarily use

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

but it seems to be an ugly hack for me. Is there a better way to do this?

Improve this question

1 Answer 1

Reset to default
4

Considering you have a DATETIME field in you database, it is probably that using LIKE (this completely depends on the database and table's engine) you are not using algorithms that are optimized to DATE operations.

One solution is to specify the whole day (from the first to the last second of it) using the BETWEEN operator that is supported by DQL as seen in it's 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 a function for that, it is also a good idea to use a function always in the value and not the column used in the condition. Some databases (like MySQL) do not use indexes in columns modified by native functions. You can create a user function called DATE that does what you expect (it could easily do the before mentioned example above) using a DQL User defined function.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

There is a DATE() function in MySQL, PostgreSQL and SQLite3, I don't have MSSQL nor Oracle at hand to test it, wonder why is it not implemented in DQL. This BETWEEN stuff seems to be better for me than the old LIKE thing until then, maybe better than my old DATE() idea.
It may be naive of my part to judge, but there are a lot of problems regarding Timezone issues. SQL does not handle this in a standard way, to ignore it would be a huge problem...
Although in an international project I would thank you for warning me, in this case this will not be a problem.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.