Uvd

Using MySQL’s DATE_FORMAT in Doctrine 2.0

Since starting out with Zend + Doctrine at the beginning of the year, all of our projects have involved administration interfaces of some description. These have, in the most part, provided our client’s with the ability to manage their application data by interfacing with filtered and paginated tables of data, such as users and contacts.

In almost all instances it has been necessary for us to filter these lists on a specific date range, (often implemented onscreen with a jQuery data picker). This undoubtedly involves comparing a date string from the HTML filters with a specific time stamp field in the database in order to select the correct data set.

In standard MySQL, my favoured technique to compare 2 timestamps was always to use the DATE_FORMAT function to convert the timestamp into an integer of format YYYYMMDD. I could then perform a simple integer comparison as follows :

where (DATE_FORMAT(user.date_registered,%Y%m%d) > 20110504)

The same technique is also very useful for collecting certain records within a time range. For example pulling all users who registered between the hours of 10am and 11am:

where (DATE_FORMAT(user.date_registered,%H%i) > 1000)
AND (DATE_FORMAT(user.date_registered,%H%i) < 1100)

To perform all of our filtered queries in Doctrine we have been using the Doctrine Query Language (DQL). DQL allows you to query your model directly using an object syntax and the Doctrine ORM converts this to an SQL statement and executes it on your database server.

I soon realised after starting out with Doctrine that, ‘out of the box’ the ORM understands only a handful of SQL functions. (I imagine these are the common ones which have the same syntax across the popular DB vendors). Unfortunately my favoured DATE_FORMAT function was not included, so I was left with 2 options to be able to compare a timestamp in my PHP code.

  • Use Doctrine’s NativeSQL functions to write an SQL statement
  • Write a User-Defined Function so that Doctrine recognises DATE_FORMAT when written in DQL and can successfully transform it into SQL.

Considering we do these comparisons so much, I went with option 2 and wrote DateFormat, a PHP class to extend Doctrines grasp of SQL

The source code is available on our Uvd Github account here : https://github.com/uvd/Doctrine

In order to configure this in your application you should:

  • Add our UVd library to you library folder
  • Ensure the UVd namespace is included has been boostrapped by your zend/doctrine application
  • Instantiate DateFormat as a custom DQL Function in your doctrine ORM boostrap process.
  • This will be dependant on your specific implemenation, but if you are using Guilherme Blanco’s Doctrine 2/Zend implementation you can add this line your application.ini
    resources.doctrine.orm.entityManagers.default.DQLFunctions.string.DATE_FORMAT = "UVd\DoctrineFunction\DateFormat"
  • If you follow the Doctrine reference manual :
    $config = new \Doctrine\ORM\Configuration();
    $config->addCustomStringFunction('DATE_FORMAT', 'UVd\DoctrineFunction\DateFormat');

You can use the function in the same way as you would in SQL, but remember your timestamp should be a reference to a PHP Object variable, not a database table field.

(DATE_FORMAT(User.dateRegistered,”%H%i”) > 1000)

Please re-use this code as you see fit, however don’t forget to drop you comments below!

  • ichr

    excellent doctrine2 extension!

    however isn’t the namespace just “namespace UVdDoctrineFunction” instead of “namespace UVdDoctrineFunctionDoctrineFunction;”at the DateFormat.php?

  • Rob

    You’re quite right, will update..thx

  • Mario

    nice date_format function man, you save my day! Thanks!

  • http://trki.sk/ Lukas Lukac

    perfect man! saved my day :) Thank you!!!