9

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.

Script47
  • 14,230
  • 4
  • 45
  • 66
JacopoStanchi
  • 1,962
  • 5
  • 33
  • 61

3 Answers3

22

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')).

JacopoStanchi
  • 1,962
  • 5
  • 33
  • 61
3

I found this solution on laracasts:

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

Bálint Budavölgyi
  • 510
  • 3
  • 18
  • But what if I don't want these timestamps to be nullable? If I want them to be set to CURRENT_TIMESTAMP for example. – JacopoStanchi May 03 '18 at 14:04
  • You can use one as current, but I don't see why would you use two as current – Bálint Budavölgyi May 03 '18 at 15:22
  • 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. – JacopoStanchi May 03 '18 at 15:25
  • 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. – Bálint Budavölgyi May 03 '18 at 15:29
  • 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. – JacopoStanchi May 03 '18 at 15:52
  • 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 – Canelo Digital Jul 16 '19 at 01:39
2

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

Giovanni S
  • 2,050
  • 18
  • 33