Set default (current datetime) to table column - CodeIgniter 3 migration

Question

I have this migration:

public function up(){
    $this->dbforge->add_field([
        'id'=>['type'=>'int', 'unique'=>true, 'unsigned'=>true,'auto_increment'=>true],
        'email'=>['type'=>'varchar', 'constraint'=>200, 'null'=>true],
        'password'=>['type'=>'varchar', 'constraint'=>250],
        'created_at'=>['type'=>'datetime', 'default' => 'CURRENT_TIMESTAMP'],
    ]);
    $this->dbforge->add_key('id', TRUE);
    $this->dbforge->create_table('users', TRUE);
}

I am trying to set table with column created_at with default value - current datetime. I am using 'default' => 'CURRENT_TIMESTAMP', but I am getting this error:

Invalid default value for 'created_at' .... NOT NULL, created_at datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP',

I am using CodeIgniter 3 with MySQL.

Barina

instead of 'created_at'=>['type'=>'datetime', 'default' => 'CURRENT_TIMESTAMP'] try 'created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP' - – Illconditioned

Hi, thx it works. It must be placed in the column name key and instead created_at will be created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP and remove array (with type, and default) in this key. – Barina

Answer 1

this worked for me, and is valid in the documentation

Passing strings as fields If you know exactly how you want a field to be created, you can pass the string into the field definitions with add_field()

$this->dbforge->add_field("label varchar(100) NOT NULL DEFAULT 'default label'");

Note: Passing raw strings as fields cannot be followed by add_key() calls on those fields.

`$this->dbforge->add_field(
  array(
    'name' => 
      array(
        'type' => 'VARCHAR',
        'constraint' => '150',
      ),
    'email' => 
      array(
        'type' => 'varchar',
        'constraint' => '150',
        'unique'=> TRUE,
    ),
    'username' => 
      array(
        'type' => 'varchar',
        'constraint' => '80',
    ),
    'created_at datetime default current_timestamp',
    'updated_at datetime default current_timestamp on update current_timestamp',
    'status' => 
      array(
        'type' => 'tinyint',
        'constraint' => '1',
    ),
  )
);`

Audre

'created_at datetime default current_timestamp', this is not working in PostgreSQL – Reluctance

is raw sql, it seems to me that the syntax for postgresql is created_at timestamp DEFAULT NOW() – Audre

this work for me, but also the option i suggested – Botulinus

Answer 2

For Codeigniter 4 this is slightly different: https://codeigniter.com/user_guide/dbmgmt/forge.html#raw-sql-strings-as-default-values

You would need something like this ...

'DateAdded'     => array(
    'type'      => 'TIMESTAMP',
    'default'   => new RawSql('CURRENT_TIMESTAMP'),
),

Corum

Answer 3

For Codeigniter 4 this is the suggestion: https://codeigniter.com/user_guide/dbmgmt/forge.html#raw-sql-strings-as-default-values

make sure to add the path... something like this ...

            'DateAdded' => [
            'type' => 'DATETIME',
            'default' => new \CodeIgniter\Database\RawSql('CURRENT_TIMESTAMP'),
        ],

Botulinus