PDOException: SQLSTATE[22007]: 1292 Incorrect datetime value on UTC ISO-8601

Hello I’m getting the following error

PDOException: SQLSTATE[22007]: 1292 Incorrect datetime value ‘2022-03-07T18:08:12.000000Z’ for column ‘created_timestamp’ at row 1 in /[path]/[to]/[project]/vendor/laravel/framework.src.Illuminate/Database/Connection.php:496

That looks like a valid UTC ISO-8601 date format to me. And in the following code

    // migration
    Schema::create('my_model', function (Blueprint $table) {
      // ...
      $table->datetime('created_timestamp');
    });

    // model
    class MyModel extends Model
    {
      protected $casts = [
        'created_timestamp' => 'datetime',
      ];
    }

    // code
    var_dump($instance->getAttribute('created_timestamp'));
    DB::table($model->getTable())->upsert(
      [$instance->toArray()],
      [$model->getKeyName()]
    );

The var dump prints a carbon instance. The docs say:

When a column is cast as a date, you may set the corresponding model attribute value to a UNIX timestamp, date string (Y-m-d), date-time string, or a DateTime / Carbon instance. The date’s value will be correctly converted and stored in your database.

The docs here https://laravel.com/docs/8.x/eloquent-mutators#date-casting-and-timezones also strongly suggest to keep the default UTC format so I don’t want to cast to a custom ‘datetime:Y-m-d H:i:s’.

So why can’t I upsert this model?

Answer

The key was to replace $instance->toArray() with $instance->getAttributes() to get MySQL friendly values w/o custom casting

Advertisement

Answer