timestamps vs datetime using Illuminate\Database\Schema\Blueprint

Laravel Version: 5.3.26
PHP Version: 7.0.14-1+deb.sury.org\~xenial+1 (works) 7.0.13-1+deb.sury.org\~trusty+1 (fails)
Database Driver & Version: MySQL 5.7.16 (Running in RDS instance in AWS)

Description:

Setting up Laravel on Homestead is working fine however, the exact same code deployed to an AWS EC2 instance running Ubuntu 14.04, Laravel 5.3.26, PHP using MySql 5.7.16 running in AWS as an RDB DB Instance has a problem dealing with timestamps.

Illuminate\Database\QueryException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value:

Can be found in the logs.

I am using Spark but I don't believe that this is a factor in this issue.

Steps to Duplicate:

The Database/migrations/create_user_table.php excerpt below shows the commands that are causing a problem:

            $table->timestamp('trial_ends_at')->nullable();
            $table->timestamp('last_read_announcements_at')->nullable();
            $table->timestamps();

$table->timestamps is a function in Illuminate\Database\Schema\Blueprint which basically adds:

            $table->timestamp('created_at')->nullable();
            $table->timestamp('updated_at')->nullable();

php artisan migrate successfully creates the table in both environments

However when trying to add data to the users table using the following:

CreateUser.php

    public function handle($request)
    {
        return Spark::interact(UserRepository::class.'@create', [$request->all()]);
    }

which somehow ends up calling this line in /vendor/laravel/framework/src/Illuminate/Database/Connection.php

  try {
            $result = $callback($this, $query, $bindings);
        }

Which ends up in the Exception

My instance running in homestead works fine, but the instance running in AWS generates the following error:

Next Illuminate\Database\QueryException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2044-04-25 09:22:28' for column 'trial_ends_at' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `last_read_announcements_at`, `trial_ends_at`, `updated_at`, `created_at`) values (Some Name, Name@domain.com, SomeEncryptedPassword, 2016-12-09 09:22:28, 2044-04-25 09:22:28, 2016-12-09 09:22:28, 2016-12-09 09:22:28)) in /var/www/html/project/vendor/laravel/framework/src/Illuminate/Database/Connection.php:769

adjusting $table->timestamp... to $table->datetime... and then running php artisan migrate:reset and then php artisan migrate allows for the application to run without issue

Questions:

Is there a database configuration that needs to be set to allow timestamp and datetime to be equivalent
Is there a way for Illuminate\Database\Schema\Blueprint\timetamps() able to be modified to create datetime instead of timestamp?
Can Illuminate\Database\Schema\Blueprint be updated to include a datetimes() function that will add in the created_at and updated_at fields using the datetime style similar to the way that timestamps adds these fields in timestamp style.

Just as a quick interjection here, this looks to be an issue with MySQL only supporting 32 bit timestamps (which stop in year 2038, so year 2044 is invalid for a 32 bit timestamp). You won't encounter this issue on created_at / updated_at unless we go past year 2038, but anything that could go past that should use DateTime (supports 0000 to 9999 year)

Generally DateTime should be used for storing specific values (if you google timestamp vs datetime mysql you'll get a lot of good posts on this).

Laravel supports using the DateTime field via dateTime (https://laravel.com/docs/5.3/migrations#creating-columns)

Thanks @david-ridgeonnet for the clarification :)

Closing this issue then, feel free to ping me though if things are still not resolved.

Laravel Version: 5.3.26

PHP Version: 7.0.14-1+deb.sury.org~xenial+1 (works) 7.0.13-1+deb.sury.org~trusty+1 (fails)

Database Driver & Version: MySQL 5.7.16 (Running in RDS instance in AWS)

Description:

Setting up Laravel on Homestead is working fine however, the exact same code deployed to an AWS EC2 instance running Ubuntu 14.04, Laravel 5.3.26, PHP using MySql 5.7.16 running in AWS as an RDB DB Instance has a problem dealing with timestamps.
Illuminate\Database\QueryException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value:
Can be found in the logs.

I am using Spark but I don't believe that this is a factor in this issue.

Steps to Duplicate:

The Database/migrations/create_user_table.php excerpt below shows the commands that are causing a problem:
            $table->timestamp('trial_ends_at')->nullable();
            $table->timestamp('last_read_announcements_at')->nullable();
            $table->timestamps();
$table->timestamps is a function in Illuminate\Database\Schema\Blueprint which basically adds:
            $table->timestamp('created_at')->nullable();
            $table->timestamp('updated_at')->nullable();
php artisan migrate successfully creates the table in both environments

However when trying to add data to the users table using the following:

CreateUser.php
    public function handle($request)
    {
        return Spark::interact(UserRepository::class.'@create', [$request->all()]);
    }
which somehow ends up calling this line in /vendor/laravel/framework/src/Illuminate/Database/Connection.php
  try {
            $result = $callback($this, $query, $bindings);
        }
Which ends up in the Exception

My instance running in homestead works fine, but the instance running in AWS generates the following error:
Next Illuminate\Database\QueryException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2044-04-25 09:22:28' for column 'trial_ends_at' at row 1 (SQL: insert into `users` (`name`, `email`, `password`, `last_read_announcements_at`, `trial_ends_at`, `updated_at`, `created_at`) values (Some Name, Name@domain.com, SomeEncryptedPassword, 2016-12-09 09:22:28, 2044-04-25 09:22:28, 2016-12-09 09:22:28, 2016-12-09 09:22:28)) in /var/www/html/project/vendor/laravel/framework/src/Illuminate/Database/Connection.php:769
adjusting $table->timestamp... to $table->datetime... and then running php artisan migrate:reset and then php artisan migrate allows for the application to run without issue

Questions:

Is there a database configuration that needs to be set to allow timestamp and datetime to be equivalent

Is there a way for Illuminate\Database\Schema\Blueprint\timetamps() able to be modified to create datetime instead of timestamp?

Can Illuminate\Database\Schema\Blueprint be updated to include a datetimes() function that will add in the created_at and updated_at fields using the datetime style similar to the way that timestamps adds these fields in timestamp style.

Did you ever find out this issue???

laravel / framework