Laravel error in creating a table with two timestamp columns

Question

I created a table in Laravel 6.6 with the following definition.

public function up()
{
    Schema::create('quarters', function (Blueprint $table) {
        $table->integer('quarter_id')->unsigned();
        $table->integer('year')->unsigned();
        $table->integer('quarter_num')->unsigned();
        $table->timestamp('valid_from');
        $table->timestamp('valid_to'); // <------ error on this line
        $table->string('description')->nullable();
        $table->timestamps();
        $table->primary('quarter_id');
    });
} 

When I run the migration command I get the following error.

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'valid_to' (SQL: create table quarters (quarter_id int unsigned not null, year int unsigned not null, quarter_num int unsigned not null, valid_from timestamp not null, valid_to timestamp not null, description varchar(255) null, created_at timestamp null, updated_at timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

here is the SQL generated by Eloquent:

CREATE TABLE `quarters`(
    `quarter_id` INT UNSIGNED NOT NULL,
    `year` INT UNSIGNED NOT NULL,
    `quarter_num` INT UNSIGNED NOT NULL,
    `valid_from` TIMESTAMP NOT NULL,
    `valid_to` TIMESTAMP NOT NULL,
    `description` VARCHAR(255) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci'

The strange thing is that if I comment out the valid_to line then it creates the table with no error. But the definition of valid_to is 100% similar to valid_from, and it does not throw that error for the valid_from column. Actually it seems the DB does not allow for twotimestamp columns!

As requested in the comments I ran the php artisan migrate --pretend and here is the result:

C:\xampp\htdocs\voiceit> php artisan migrate --pretend
CreateQuartersTable: create table `quarters` (`quarter_id` int unsigned not null, `year` int unsigned not null, `quarter_num` int unsigned not null, `valid_from` timestamp not null, `valid_to` timestamp not null, `description` varchar(255) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
CreateQuartersTable: alter table `quarters` add primary key `quarters_quarter_id_primary`(`quarter_id`)
CreatePeopleDatasTable: create table `people_datas` (`mt_id` bigint unsigned not null, `valid_for` int unsigned not null, `local_personal_id` bigint unsigned not null, `first_name` varchar(255) not null, `last_name` varchar(255) not null, `date_of_birth` date null, `date_of_join` date null, `gender` varchar(1) not null, `location_type` varchar(1) not null, `created_at` timestamp null, `updated_at` timestamp null, `deleted_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
CreatePeopleDatasTable: alter table `people_datas` add primary key `people_datas_mt_id_valid_for_primary`(`mt_id`, `valid_for`)
CreatePeopleDatasTable: alter table `people_datas` add constraint `people_datas_valid_for_foreign` foreign key (`valid_for`) references `quarters` (`quarter_id`)
CreatePeopleDatasTable: alter table `people_datas` add constraint `people_datas_gender_foreign` foreign key (`gender`) references `genders` (`id`)
CreatePeopleDatasTable: alter table `people_datas` add constraint `people_datas_location_type_foreign` foreign key (`location_type`) references `location_types` (`id`)
CreatePeopleDatasTable: alter table `people_datas` add index `people_datas_last_name_index`(`last_name`)

Answer 1

I solved my issue by changing the type of the column from timestamp to dateTime. So changing the table definition as follow solved my issue as I need a date and a time:

Schema::create('quarters', function (Blueprint $table) {
             $table->integer('quarter_id')->unsigned();
             $table->integer('year')->unsigned();
             $table->integer('quarter_num')->unsigned();
             $table->dateTime('valid_from');
             $table->dateTime('valid_to');            
             $table->string('description')->nullable();
             $table->timestamps();
             $table->primary('quarter_id');
        });

However, I am still interested to know how can we have multiple not null timestamp columns in a table.

Answer 2

The default behaviour for default values of timestamps in MySql and/or MariaDB is different for the first timestamp declaration than for subsequent timestamps.

MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table. For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This means that if the column is not explicitly assigned a value in an INSERT or UPDATE query, then MariaDB will automatically initialize the column's value with the current date and time.

This automatic initialization for INSERT and UPDATE queries can also be explicitly enabled for a column that uses the TIMESTAMP data type by specifying the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses for the column. In these clauses, any synonym of CURRENT_TIMESTAMP is accepted, including CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

https://mariadb.com/kb/en/library/timestamp/

Ergo, the first timestamp (valid_from) gets the automatic default value of CURRENT_TIMESTAMP, which is an acceptable default value for non-nullable timestamp fields. The second field gets a different automatic default value, which appears to be unacceptable to the database.

A solution to the immediate problem might be to follow MariaDB's suggestion and use an explicit CURRENT_TIMESTAMP as default for the second timestamp (or both, while at it). In Laravel terms, this is probably something like $table->timestamp('valid_to')->useCurrent();.

Important to notice in this regard, is that the solution you seem to have chosen in the end (using datetime over timestamp) is probably a more apt solution to the problem: timestamps are somewhat weird data types that are mainly used as meta-data only. Especially in Mysql and MariaDB, they inherently suffer from the "Year 2038 problem", which isn't a problem for created_at or updated_at fields for another 18 years or so, but might be problematic when the valid_to can be some years in the future. And that's only in addition to the unintuitive automatic default values...

Answer 3

You can solve it add or modify the server variable "explicit-defaults-for-timestamp"... You need set it to ON

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp