Deleting records with a limited life time

Published on and tagged with cakephp  sql

Just a short tip today.

During the NoseRub DevCamp last weekend (here a short summary) I had to implement request tokens with a limited life time. In our case this life time is 24 hours, i.e. the user has to use the request token within 24 hours, or else it expires. To remove the expired request tokens I wrote the following method:

// in the RequestToken model
public function deleteExpired() {
    $this->deleteAll(array('RequestToken.modified <= DATE_SUB(NOW(), INTERVAL 24 HOUR)'));
}

This snippet performs the following steps: it gets the current date and time, subtracts 24 hours, and then deletes all request tokens whose “modified” value is smaller (i.e. older) or equal to the result of the subtraction.

MySQL’s date and time functions are much more powerful than shown in this example, so I recommended to check out the documentation, too.

I hope this is helpful for some of you :)

7 comments baked

  • rafaelbandeira3

    good one Daniel, docs on MySQL Date and Time functions are a must-read for every dev. In my current – and endless – project I moved lots of time and date handling from the app layer – good & old php – to db queries, and I must say, the gain is not only on performance but in readability and api size for models.

    Now just a buggy thought: Why not follow the pattern jQuery follows – I don’t know if it has a name – and that we already do with Model::find(), letting all related operations in just one method, then your method would be more like:

    function deleteAll($conditions, $cascade = true, $callbacks = false) {
         if($conditions === 'expired') {
              $conditions = array('RequestToken.modified <= DATE_SUB(NOW(), INTERVAL 24 HOUR)');
         } elseif($conditions === 'invalid') {
              $conditions = array('CheeseCake.flavor != "cheese"');
         }
         return parent::deleteAll($conditions, $cascade, $callbacks);
    }

    What do you think? I think it’s nice

  • Pixelastic

    Nice one, I was trying to do the same thing by using the array(‘conditions’ => array()) syntax but it didn’t work because fields were treated as string (and escaped with ‘)

    I had to make my calculation directly in php with mktime() and date() before putting it in the query, but your approach is nice

  • cakebaker

    @Rafael, Pixelastic: Thanks for your comments!

    @Rafael: Yes, the date and time functions are very powerful.

    I don’t know the official name of that pattern, but I think it is a kind of a facade. And yes, it is a possible way for the implementation. Personally, I’m not a big fan of using this approach to reduce the number of public methods of a class, as it makes your API implicit, i.e. you have to look in the source or the documentation to figure out what the method does…

  • Pixelastic

    As I’ve just realized, uploading my app from prod to dev, this approach seems to have its limits when your mysql server is in another timezone than your visitors.

    I have a site where items are displayed based on the time they where modified (auto-populated field modified of cakePHP).

    First problem, dates were stored in the database according to the server where PHP were running.
    I had to change the timezone used by php by putting a SetEnv TZ Europe/Paris in my .htaccess and a date_default_timezone_set(‘Europe/Paris’); in my bootstrap.php to correct that.

    But as my app is running on a shared server, I can’t change the mysql timezone (only root can do that), thus can’t use mysql built-in NOW(), INTERVAL() and such functions.
    So I had to re-re-write back all my SELECT queries, using php generated dates instead of mysql built-in ones.

    Or maybe did I overlooked something ?

  • cakebaker

    @Pixelastic: Yes, as soon as your server is in a different timezone than your visitors and you want to show them date-related things it becomes tricky.

    One approach is, as you did, to deal with all date-related stuff directly in PHP. A different approach could be to work with the difference between the timezone of the server and the timezone of the visitors. Everytime you read dates from the database you add/subtract the difference.

    But I have to admit that I don’t have any real experience with such scenarios…

  • Brett ODonnell

    I prefer to use strtotime() like this:

    // in the RequestToken model
    public function deleteExpired() {
        $this->deleteAll(array('RequestToken.modified <=' => date('Y-m-d H:i:s',strtotime('-24 hours'))));
    }
  • cakebaker

    @Brett: Yes, that’s a feasible way, too. Thanks for sharing!

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License