How to select for a specific date or time with Doctrine 2.0
I am really a fan of Doctrine 2.0 and even more so of its documentation! It explains everything you need to know and serves as a great reference. It even manages to feel more lightweight and less complex than doctrine 1.2 did.
One thing though seems to be missing from the docs: How can I do date and time arithmetics?
The answer is simple but was available to me only via IRC..
In fact it took about two minutes between connecting to IRC and disconnecting again with the answer in my head. Thanks folks, that’s awesome!
So I have this big table with stuff in it and one of its columns is a date field. Now the docs will help you to know how to select something for the current date easily. You use the build in DQL function CURRENT_DATE(). That was easy!
But now I needed to fetch some stuff for tomorrow and I was lost. There is no more build in DQL function and simple strings won’t work nor will timestamps..
But hey, I remembered that Doctrine makes a lot of use of PHPs native DateTime type! So I tried the following.
With the following result:
Catchable fatal error: Object of class DateTime could not be converted to string in /var/www/ical.ly/programmat/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php on line 574
This was the time when I turned to the #doctrine channel to ask my question. And the answer could not be simpler!
So there is a third parameter that can be used to define the type of parameter that is set which obviously defaults to string!
Now you can fully leverage all the possibilities a DateTime object offers and Doctrine will know how to handle it.