Richardds's avatar

Invalid datetime format

Hello, I am having a problem with seeding of a database. I defined Eloquent model as it is described in documentation but when i run the command artisan migrate:fresh --seed i get the following error:

[Illuminate\Database\QueryException]                                                                                 
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '30. September 2017 10:24:25' for column '  
  updated_at' at row 1 (SQL: insert into `dns_zones` (`name`, `admin`, `serial`, `refresh`, `retry`, `expire`, `ttl`,  
   `updated_at`, `created_at`) values (test.example, admin.test.example, 19910907, 86400, 7200, 8640000, 345600, 30. Sept  
  ember 2017 10:24:25, 30. September 2017 10:24:25))

[Doctrine\DBAL\Driver\PDOException]                                                                                  
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '30. September 2017 10:24:25' for column '  
  updated_at' at row 1

[PDOException]                                                                                                       
  SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '30. September 2017 10:24:25' for column '  
  updated_at' at row 1

Here is the model definition:

class DnsZone extends Model
{
    protected $fillable = [
        'name',
        'admin',
        'serial',
        'refresh',
        'retry',
        'expire',
        'ttl'
    ];

    protected $dates = [
        'created_at',
        'updated_at',
        'deleted_at'
    ];

    protected $casts = [
        'created_at' => 'date',
        'updated_at' => 'date',
        'deleted_at' => 'date'
    ];

    protected $dateFormat = 'j. F Y H:i:s';

    protected static function boot()
    {
        parent::boot();
        static::addGlobalScope(new DisabledScope());
    }
}

Here is the code of the seeder:

$factory->define(\Example\DnsZone::class, function (Faker $faker) {
    $name = $faker->domainName;
    return [
        'name' => $name,
        'admin' => 'admin.' . $name,
        'serial' => intval($faker->year . $faker->month . $faker->dayOfMonth),
        'refresh' => $faker->numberBetween(1, 8) * 60 * 60 * 6, // 6, 12, 18 hours ...
        'retry' => $faker->numberBetween(1, 8) * 60 * 30, // 30, 60, 90 minutes ...
        'expire' => $faker->numberBetween(1, 4) * 60 * 60 * 600, // 600, 1200, 1800 hours ...
        'ttl' => $faker->numberBetween(1, 8) * 60 * 60 * 12 // 12, 24, 36 hours ...
    ];
});

It is only possible to successfully run migration when I remove:

$dateFormat = 'j. F Y H:i:s'

Why?

Any tip appreciated.

Richard

0 likes
2 replies
shakti's avatar

You need to format date like "Y-m-d H:i:s" in order to work with MySQL datetime field. From documentation :

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

1 like
Richardds's avatar

I thought php $dateFormat variable is a default formatting for a date accessor method. I found out I need to define the following accessor to accomplish what i want.

public function get{attribute_name}Attribute(Carbon $value) {
    return $value->format('j. F Y H:i:s');
}
1 like

Please or to participate in this conversation.