Laravel 10.47中如何为PostgreSQL正确设置时间戳字段种子？

环境信息 #

• Laravel 10.47
• PostgreSQL 14.2

迁移代码 #

$table->timestamp('email_verified_at')->nullable()->useCurrentOnUpdate();
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->nullable()->useCurrentOnUpdate();
$table->softDeletes()->nullable()->useCurrentOnUpdate();

种子场景与问题 #

场景1：不手动填充时间戳字段 #

仅created_at会被自动填充，其余email_verified_at、updated_at、deleted_at字段为NULL。

场景2：使用Carbon::now()->timestamp填充时间戳字段 #

执行种子时抛出以下错误：

Database\Seeders\UserTableSeeder ......................................................................................................... RUNNING  

   Illuminate\Database\QueryException 

  SQLSTATE[22008]: Datetime field overflow: 7 ERROR:  date/time field value out of range: "1711657211"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  unnamed portal parameter $2 = '...' (Connection: pgsql, SQL: insert into "users" ("avatar", "deleted_at", "email", "email_verified_at", "last_name", "middle_name", "name", "password", "remember_token", "role", "updated_at") values (prof1-Picture-S3, 1711657211, Prof1@gmail.com, 1711657211, Prof1, Prof1, Prof1, $2y$12$n9zt3JXLCCqP.N0LY2SVIeq1n.w3ifZQpTa2pWxo0lZKh7vzSAsl2, some remember_token STRING, 0, 1711657211), (prof2-Picture-S3, 1711657211, Prof2@gmail.com, 1711657211, Prof2, Prof2, Prof2, $2y$12$aPCTu8oup/PraiTE5cvQwOfUCOoThYhDF7gdyxTvuKGov0gFFPKD6, some remember_token STRING, 1, 1711657211), (Paci1-Picture-S3, 1711657212, Paci1@gmail.com, 1711657212, Paci1, Paci1, Paci1, $2y$12$pouVr40UAxCjtfQFd.srkeBo83Zm.ewYcE2iQeC9u7yue/OvoNte2, some remember_token STRING, 2, 1711657212))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:829
    825▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    826▕                 );
    827▕             }
    828▕ 
  ➜ 829▕             throw new QueryException(
    830▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    831▕             );
    832▕         }
    833▕     }

      +7 vendor frames 

  8   database/seeders/UserTableSeeder.php:27
      Illuminate\Database\Query\Builder::insert()
      +8 vendor frames 

  17  database/seeders/DatabaseSeeder.php:16
      Illuminate\Database\Seeder::call("Database\Seeders\UserTableSeeder")

补充测试结果 #

正常运行场景（替换useCurrentOnUpdate()为useCurrent()） #

迁移代码：

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

种子代码：不手动填充email_verified_at、created_at、updated_at、deleted_at字段，执行正常。

异常触发场景（保留useCurrentOnUpdate()） #

迁移代码：

$table->timestamp('email_verified_at')->nullable()->useCurrentOnUpdate();
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->nullable()->useCurrentOnUpdate();
$table->softDeletes()->nullable()->useCurrentOnUpdate();

种子代码：

'email_verified_at' => Carbon::now()->timestamp,
'updated_at' => Carbon::now()->timestamp,
'deleted_at' => Carbon::now()->timestamp

执行时触发SQLSTATE[22008]: Datetime field overflow错误。

解决方案 #

问题根源 #

PostgreSQL的timestamp字段接收日期时间格式字符串，而Carbon::now()->timestamp返回的是Unix时间戳（整数），格式不匹配导致数据库无法解析，抛出溢出错误。另外，useCurrentOnUpdate()仅在更新操作时自动填充当前时间，插入操作不会自动赋值，这也是场景1中仅created_at被填充的原因。

无错误的种子填充方法 #

1. 直接使用Carbon实例赋值
   Laravel会自动将Carbon对象转换为PostgreSQL支持的日期时间格式：

'email_verified_at' => Carbon::now(),
'updated_at' => Carbon::now(),
'deleted_at' => Carbon::now(),

2. 为迁移字段同时设置插入和更新默认值
   如果希望插入时自动填充字段，同时保留更新时自动更新的特性，可以在迁移中同时添加useCurrent()和useCurrentOnUpdate()：

$table->timestamp('email_verified_at')->nullable()->useCurrent()->useCurrentOnUpdate();
$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->nullable()->useCurrent()->useCurrentOnUpdate();
$table->softDeletes()->nullable()->useCurrentOnUpdate();

这样插入时email_verified_at和updated_at会自动填充当前时间，更新时也会自动更新，种子代码中无需手动赋值这些字段。

3. 手动转换为日期时间字符串（不推荐）
   若坚持使用Unix时间戳，需先转换为PostgreSQL支持的格式：

'email_verified_at' => Carbon::createFromTimestamp(Carbon::now()->timestamp)->toDateTimeString(),

内容的提问来源于stack exchange，提问作者IgorAlves