Use Unix timestamp in Doctrine Timestampable

This is a question that might get an answer easier than what I first thought, actually...

Let's begin by what you have now :

• a model class, that extends Doctrine_Record
  • I will call this class Test, for my example(s).
  • In this Test model, you want to use the Timestampable Behaviour, but with UNIX timestamps, and not datetime values
  • And you want this without having to write lots of configuration stuff in your models.
    (I can understand that : less risk to forget one line somewhere and at wrong data in DB)
• A project that is configured and everything
  • which means you know you stuff with Doctrine
  • and that I won't talk about the basics

A solution to this problem would be to not use the default Timestampable behaviour that comes with Doctrine, but another one, that you will define.
Which means, in your model, you will have something like this at the bottom of setTableDefinition method :

$this->actAs('MyTimestampable');

(I suppose this could go in the setUp method too, btw -- maybe it would be it's real place, actually)

---

What we now have to do is define that MyTimestampable behaviour, so it does what we want.
As Doctrine's Doctrine_Template_Timestampable already does the job quite well (except for the format, of course), we will inherit from it ; hopefully, it'll mean less code to write ;-)

So, we declare our behaviour class like this :

class MyTimestampable extends Doctrine_Template_Timestampable
{
    // Here it will come ^^
}

Now, let's have a look at what Doctrine_Template_Timestampable actually does, in Doctrine's code source :

• a bit of configuration (the two created_at and updated_at fields)
• And the following line, which registers a listener :

$this->addListener(new Doctrine_Template_Listener_Timestampable($this->_options));

Let's have a look at the source of this one ; we notice this part :

if ($options['type'] == 'date') {
    return date($options['format'], time());
} else if ($options['type'] == 'timestamp') {
    return date($options['format'], time());
} else {
    return time();
}

This means if the type of the two created_at and updated_at fields is not date nor timestamp, Doctrine_Template_Listener_Timestampable will automatically use an UNIX timestamp -- how convenient !

---

As you don't want to define the type to use for those fields in every one of your models, we will modify our MyTimestampable class.
Remember, we said it was extending Doctrine_Template_Timestampable, which was responsible of the configuration of the behaviour...

So, we override that configuration, using a type other than date and timestamp :

class MyTimestampable extends Doctrine_Template_Timestampable
{
    protected $_options = array(
        'created' =>  array('name'          =>  'created_at',
                            'alias'         =>  null,
                            'type'          =>  'integer',
                            'disabled'      =>  false,
                            'expression'    =>  false,
                            'options'       =>  array('notnull' => true)),
        'updated' =>  array('name'          =>  'updated_at',
                            'alias'         =>  null,
                            'type'          =>  'integer',
                            'disabled'      =>  false,
                            'expression'    =>  false,
                            'onInsert'      =>  true,
                            'options'       =>  array('notnull' => true)));
}

We said earlier on that our model was acting as MyTimestampable, and not Timestampable... So, now, let's see the result ;-)

If we consider this model class for Test :

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->setTableName('test');
        $this->hasColumn('id', 'integer', 4, array(
             'type' => 'integer',
             'length' => 4,
             'unsigned' => 0,
             'primary' => true,
             'autoincrement' => true,
             ));
        $this->hasColumn('name', 'string', 32, array(
             'type' => 'string',
             'length' => 32,
             'fixed' => false,
             'primary' => false,
             'notnull' => true,
             'autoincrement' => false,
             ));
        $this->hasColumn('value', 'string', 128, array(
             'type' => 'string',
             'length' => 128,
             'fixed' => false,
             'primary' => false,
             'notnull' => true,
             'autoincrement' => false,
             ));
        $this->hasColumn('created_at', 'integer', 4, array(
             'type' => 'integer',
             'length' => 4,
             'unsigned' => 0,
             'primary' => false,
             'notnull' => true,
             'autoincrement' => false,
             ));
        $this->hasColumn('updated_at', 'integer', 4, array(
             'type' => 'integer',
             'length' => 4,
             'unsigned' => 0,
             'primary' => false,
             'notnull' => false,
             'autoincrement' => false,
             ));
        $this->actAs('MyTimestampable');
    }
}

Which maps to the following MySQL table :

CREATE TABLE  `test1`.`test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) NOT NULL,
  `value` varchar(128) NOT NULL,
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

We can create two rows in the table this way :

$test = new Test();
$test->name = 'Test 1';
$test->value = 'My Value 2';
$test->save();

$test = new Test();
$test->name = 'Test 2';
$test->value = 'My Value 2';
$test->save();

If we check the values in the DB, we'll get something like this :

mysql> select * from test;
+----+--------+----------------+------------+------------+
| id | name   | value          | created_at | updated_at |
+----+--------+----------------+------------+------------+
|  1 | Test 1 | My Value 1     | 1248805507 | 1248805507 |
|  2 | Test 2 | My Value 2     | 1248805583 | 1248805583 |
+----+--------+----------------+------------+------------+
2 rows in set (0.00 sec)

So, we are OK for the creation of rows, it seems ;-)

---

And now, let's fetch and update the second row :

$test = Doctrine::getTable('Test')->find(2);
$test->value = 'My New Value 2';
$test->save();

And, back to the DB, we now get this :

mysql> select * from test;
+----+--------+----------------+------------+------------+
| id | name   | value          | created_at | updated_at |
+----+--------+----------------+------------+------------+
|  1 | Test 1 | My Value 1     | 1248805507 | 1248805507 |
|  2 | Test 2 | My New Value 2 | 1248805583 | 1248805821 |
+----+--------+----------------+------------+------------+
2 rows in set (0.00 sec)

The updated_at field has been updated, and the created_at field has not changed ; which seems OK too ;-)

---

So, to make things short, fit in a couple of bullet points, and summarize quite a bit :

• Our model class acts as our own MyTimestampable, and not the default Timestampable
• Our behaviour extends Doctrine's one
• And only override it's configuration
  • So we can use it as we want, with only one line of code in each one of our models.

I will let you do some more intensive tests, but I hope this helps !
Have fun :-)