Email iconarrow-down-circleGroup 8Path 3arrow-rightGroup 4Combined Shapearrow-rightGroup 4Combined ShapeUntitled 2Untitled 2Path 3ozFill 166crosscupcake-icondribbble iconGroupPage 1GitHamburgerPage 1Page 1LinkedInOval 1Page 1Email iconphone iconPodcast ctaPodcast ctaPodcastpushpinblog icon copy 2 + Bitmap Copy 2Fill 1medal copy 3Group 7twitter icontwitter iconPage 1

Warning: this article was posted in August 2011 and some parts may now be out of date

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 = "UVdDoctrineFunctionUnixTimestamp"
  • If you follow the Doctrine reference manual :
    $config = new DoctrineORMConfiguration();
    $config->addCustomStringFunction('UNIX_TIMESTAMP', 'UVdDoctrineFunctionUnixTimestamp');

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!

Share: