Using MySQL’s UNIX_TIMESTAMP in Doctrine 2.0

I’ve been working with timestamps quite a lot recently and although my Doctrine DATE_FORMAT function is proving pretty useful, I came across a bit of stumbling block today where it could not help….the unix timestamp format. I needed to perform a comparison using UNIX Timestamps, which cannot be generated by DATE_FORMAT.
In raw SQL , you can use the function UNIX_TIMESTAMP to do this. However, similarly to DATE_FORMAT, UNIX_TIMESTAMP has not been included as a standard function in Doctrine 2….Therefore, a new User-Defined function was required.

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 UnixTimestamp 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.UNIX_TIMESTAMP = "UVd\DoctrineFunction\UnixTimestamp"

• If you follow the Doctrine reference manual :

  $config = new \Doctrine\ORM\Configuration();
  $config->addCustomStringFunction('UNIX_TIMESTAMP', 'UVd\DoctrineFunction\UnixTimestamp');

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.

• UNIX_TIMESTAMP(User.dateRegistered)

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

This entry was posted on August 10th, 2011 and is filed under Code and tagged as: doctrine, frameworks, github, mysql, unix timestamp.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or Trackback from your own site.