Eloquent: Invalid default value with timestamps

Question

Here's my migration schema:

public function up()
{
    Schema::create('objects', function (Blueprint $table) {
        $table->increments('id');
        $table->timestamp('timestamp1');
        $table->timestamp('timestamp2');
    });
}

But when I execute php artisan migrate, I get this error:

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'timestamp2' (SQL: create table objects (id int unsigned not null auto_increment primary key, timestamp1 timestamp not null, timestamp2 timestamp not null) default character set utf8mb4 collate utf8mb4_unicode_ci)

I must indicate that when I remove one of the 2 $table->timestamp(...); lines it works, but it doesn't when there is both. And the Object.php model is empty as it can be. Did I make a mistake?

I have read this post, but even though there is no longer errors when I change timestamp(...) into dateTime(...), I only want timestamps.

Breckenridge

The create statement works fine for me. What version of mysql are you using? – Hydrokinetic

There will be only one timestamp col in a table so make it CHAR the update the data type of the col. – Bitty

Answer 1

Timestamps are a little special, they must either be nullable or they must have a default value. So you must choose between timestamp('timestamp1')->nullable(); or timestamp('timestamp1')->useCurrent() or a custom default value like timestamp('timestamp1')->default(DB::raw('2018-01-01 15:23')).

Breckenridge

I think all data types must be either nullable or have a default value. – Cheju

That's not true, VARCHAR and INT for example can be non-nullable and without a default value. You just have to fill the value during insertion. – Breckenridge

WTF Mysql!? Why can't we just require that a timestamp has to be set on insert like every other column? What a headache. – Interweave

Answer 2

I found this solution on laracasts:

nullableTimestamps() are only for default fields created_at, updated_at. for custom fields use timestamp()->nullable();

Dogvane

But what if I don't want these timestamps to be nullable? If I want them to be set to CURRENT_TIMESTAMP for example. – Breckenridge

You can use one as current, but I don't see why would you use two as current – Cheju

I don't know, I find it strange that for example INT fields do not have to have a default value, but it is mandatory for timestamps. – Breckenridge

The very first non-null field will be set to use CURRENT_TIMESTAMP as default. I suppose everything should have a default value if not nullable. – Cheju

I posted a new answer because I didn't know if you would be OK if I edited your answer as there might be mistakes. – Breckenridge

The problem can arise if your DB-scheme is not english but, let's say, spanish... from a consistency point of view you'd like to have every column in the same language, so if you want to have a column named: 'creado' instead of 'created_at', laravel allows to define that in the model, but as we also see, the migration file does not recognize that... I am having the same issue, trying to use consistently spanish column names, but this date issue makes me doubt, whether to revert all created/odified columns to english, just to have no problem with this nullable() issue – Ybarra

Answer 3

You can make one of the two timestamps nullable by using

timestamp()->nullable();

using your example, you would use:

$table->timestamp('timestamp2')->nullable();

Also laravel has built in timestamps by using

$table->timestamps();

which would automatically handle updated_at and created_at timestamping for you

Sail

I know for timestamps(), I just wanted to make my example simple. – Breckenridge