Yii2 : Add ON UPDATE CURRENT_TIMESTAMP attribute

Question

I am extending yii\db\Migration class to add a method timestamps which will speed up me creating migrations. It will add all timestamps I need in every migration I will create.

I have seen this functionality in Laravel and CakePHP frameworks and I'm curious why this is not available by default within Yii 2 Migration Tool.

I have tried the following:

namespace custom\db;

use \yii\db\Migration as YiiMigration;

class Migration extends YiiMigration
{
    public function timestamps($tableName)
    {
        $this->addColumn(
            $tableName,
            'created_at',
            $this->timestamp()->null()->defaultExpression('CURRENT_TIMESTAMP')
        );

        $this->addColumn(
            $tableName,
            'updated_at',
            $this->timestamp()->null()
        );

        $this->addColumn(
            $tableName,
            'deleted_at',
            $this->timestamp()->null()->defaultExpression('NULL')
        );
    }
}

In my actual migration in up or safeUp method I do the following:

public function safeUp()
{
    $this->createTable('test', [
        'id' => 'pk',
    ]);

    $this->timestamps('test');
}

When I run this, fields created_at and deleted_at get their types and default values as specified. created_at is nullable but it's default value is CURRENT_TIMESTAMP and deleted_at is nullable and it's default value is NULL.

Problem is with updated_at field. I do not know the way to set attributes to that field with Yii 2 migrations and I need to set: ON UPDATE CURRENT_TIMESTAMP attribute, which will always change the value when the record is updated.

Now, this goes even further. When I was testing this functionality only with using created_at field and with following options, that field would always get the attribute ON UPDATE CURRENT_TIMESTAMP:

$this->addColumn(
    $tableName,
    'created_at',
    $this->timestamp()
);

And yes, the field is not nullable and it contains the attribute I need. And that still is not what I need because I need the field nullable, followed by that attribute.

And for the end, the worst part...

I have tried doing the following for the updated_at, hoping that it will follow what happend to created_at:

$this->addColumn(
    $tableName,
    'updated_at',
    $this->timestamp()
);

Now the default value in the table is: 0000-00-00 00:00:00 and it's not nullable.

What is happening here, I do not have any clue anymore.

What am I doing wrong here and how to do this right?

Answer 1

You need to set the default value explicitly using the ->defaultValue(null) and then you need to use the ->append('ON UPDATE CURRENT_TIMESTAMP') in the following way for the $type parameter,

$this->addColumn(
    $this->_table,
    'updated_at',
    $this->timestamp()->defaultValue(null)->append('ON UPDATE CURRENT_TIMESTAMP')
);

The above will show you the field in phpmyadmin like below

See these discussions - https://github.com/bizley/yii2-migration/issues/6