Resolving "Incorrect datetime value" in Laravel

timezone-laravel-fix.md

While importing data into a local MySQL database in Laravel I hit this issue:

PDOException::("SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-03-11 02:04:15' for column 'created_at' at row 825")

The problem is due to daylight savings time (DST). In OSX MySQL is generally set to use SYSTEM time rather than UTC.

So, in my case, the issue was that datetime 2018-03-11 02:04:15 technically did not exist in CST.

One suggested solution is to update my.conf to use timezone = UTC, but I could not track down a my.conf file to use or modify (MySQL had been installed with Brew).

But Laravel offers another option. In config/database.php, just add this line to your mysql driver:

'timezone'  => '+00:00'

The full text should look something like this:

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
    'timezone'  => '+00:00'
],

That seemed the easiest and worked for me.

Mega thanks for sharing this.