UVD | Using MySQL's UNIX_TIMESTAMP in Doctrine 2.0

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 UV^d 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.