test.ical.ly | getting the web by the balls

Mar/11

25

How to select for a specific date or time with Doctrine 2.0

10 Comments · Posted by Christian in The real job

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.

·



  • http://www.weirdog.com Olivier Laviale

    Wouldn’t it be nice if DateTime were to implement __toString() and return “2011-03-25 09:30:12″ ?

  • beberlei

    There is also the following ticket for arethmetics support for 2.1:

    http://www.doctrine-project.org/jira/browse/DDC-1014

    Feel free to contribute :-)

  • http://test.ical.ly Christian

    @Oliver well you can simply extend \DateTime and implement __toString() yourself.

    i.e.
    class MyDateTime extends \DateTime
    {
    public function __toString()
    {
    return $this->format(‘Y-m-d H:i:s’);
    }
    }

  • http://test.ical.ly Christian

    Update: the above works well for “datetime” fields. If you want to work with “date” fields then you have to use “\Doctrine\DBAL\Types\Type::DATE” instead.

  • Pingback: • Beware of the timezone! Working with PHP DateTime and Doctrine for MongoDB can cause conflicts | test.ical.ly

  • http://blog.bisna.com Guilherme Blanco

    As of 2.1, we have what we call as ParameterTypeInferer. This feature provides a built-in functionality that automatically infers the parameter given and automatically defines the type (the third parameter), if not provided.

    Based on that, you won’t suffer anymore of that exception. =)

  • http://test.ical.ly Christian

    @Guilherme now that is just awwwwesome! :)

  • http://www.hostingnuggets.com/ Web Hosting

    Ah, you saved my life, no need of that stupid MySQL NOW() anymore I can just use PHP’s native DateType!

  • Alice

    Very-very big thanks! :)

  • Pierre

    Perfect tip ! You save my day :)

<<

>>

Find it!

Theme Design by devolux.nh2.me
To top