Level 29
As a workaround, you could use nullable timestamps, in which case MySql will not use CURRENT_TIMESTAMP as default.
Mar 14, 2016
9
Level 3
Multiple timestamps in one table
I have just read: https://github.com/laravel/framework/issues/11518 and have been left more confused than I began. It seems there are issues with certain versions of MySQL and Laravel with regards to default values on timestamp fields.
I have a tasks table as follows:
Schema::create('tasks', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->text('content');
$table->datetime('deadline');
$table->datetime('reminder');
$table->integer('user_id')->index();
$table->timestamps();
});
What are the pros and cons of using datetime vs timestamp for the 'deadline' and 'reminder' fields? In order to use Laravel's built-in Carbon handling (by adding these fields to $dates) I understand they need to be timestamps. But setting these as timestamps causes unexpected behaviour in that it specifies for the first field (i.e. deadline) CURRENT_TIMESTAMP on update and also a default CURRENT_TIMESTAMP which is undesired behaviour.
My workaround of the issues in the link above are currently to stick to using datetime and initialising new Carbon objects in my Attribute methods (i.e. not being able to use $dates for my own fields for now).
Level 3
Do I use nullableTimestamps() instead of timestamps() or also for my deadline and reminder fields?
Level 29
Yes, or else the very first non-null field will be set to use CURRENT_TIMESTAMP as default.
Edit: BTW.
nullableTimestamps() are only for default fields created_at, updated_at. for custom fields use timestamp()->nullable();
1 like
Level 3
I am now trying:
Schema::create('tasks', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->text('content');
$table->nullableTimestamps('deadline');
$table->nullableTimestamps('reminder');
$table->integer('user_id')->index();
$table->nullableTimestamps('updated_at');
$table->nullableTimestamps('created_at');
});
But I get this error when running php artisan migrate:refresh :
[Illuminate\Database\QueryException]
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'created_at' (SQL: create table tasks (id int unsigned not null auto_increment primary key, title varchar(255) n
ot null, content text not null, created_at timestamp null, updated_at timestamp null, created_at timestamp null, updated_at timestamp null, user_id int not null, created_at timestamp null, updated_at timestamp null, created_at timestamp null, updated_at timestamp null) default character set utf8 collate utf8_unicode_ci)
Level 3
Not sure why it's duplicating the fields?
Level 29
See the edited answer...
Level 3
Okay so I have this now which seems to have worked:
Schema::create('tasks', function (Blueprint $table) {
$table->increments('id');
$table->string('title');
$table->text('content');
$table->timestamp('deadline')->nullable();
$table->timestamp('reminder')->nullable();
$table->integer('user_id')->index();
$table->nullableTimestamps();
});
One further question...
How does Laravel update the updated_at fields? Does it rely on MySQL or does it pass an updated timestamp when an update is made? I ask because it is just a normal timestamp field so MySQL will not update it by itself.
Level 29
Does it rely on MySQL.
No, the timestamp is touched every time an update is made.
1 like
Level 3
Thank you premsaurav. You saved me a lot of time banging my head against a wall trying to figure this out. I hope it is fixed by default in Laravel soon though.
Please or to participate in this conversation.