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!