Laravel迁移创建的timestamp字段存时超前6小时如何解决

Hey there! Let's get that timestamp timezone mismatch sorted out. The 6-hour gap you're seeing almost always comes down to conflicting timezone settings between your Laravel app, server, or database. Here's how to tackle it step by step:

1. Set the Correct Timezone in Laravel #

First, make sure your Laravel application is using your local timezone.

• Open your .env file and update the APP_TIMEZONE variable to match your local timezone identifier (use IANA Time Zone Database names like Asia/Shanghai, Europe/London, etc.):

  APP_TIMEZONE=Asia/Shanghai
  

• If you don't use .env or want to set it directly, edit config/app.php and update the timezone key:

  'timezone' => 'Asia/Shanghai',
  

• After making changes, clear the configuration cache to apply them immediately:

  php artisan config:clear
  

2. Verify Database Timezone Settings #

Your database might be running on a different timezone than your app, which causes the offset. Let's check and fix this for common databases:

For MySQL/MariaDB #

• Run this query in your database client to check current timezone settings:

  SELECT @@global.time_zone, @@session.time_zone;
  

• If the result isn't your local timezone, set it permanently:
  • Open your database config file (my.cnf on Linux, my.ini on Windows) and add this line under the [mysqld] section:

    default-time-zone='Asia/Shanghai'
    

  • Restart your database server to apply the change.
• For a temporary fix (until server restart), run these queries:

  SET GLOBAL time_zone = 'Asia/Shanghai';
  SET time_zone = 'Asia/Shanghai';
  

For PostgreSQL #

• Check the current timezone with:

  SHOW TIMEZONE;
  

• Set it to your local timezone:

  SET TIMEZONE='Asia/Shanghai';
  

• To make it permanent, update the postgresql.conf file's timezone parameter and restart the service.

For SQLite #

SQLite uses the server's system timezone by default. Make sure your server's system clock is set to the correct timezone.

3. Ensure Model Timestamp Behavior Aligns With Your Needs #

Laravel automatically manages timestamps, but sometimes you might need to confirm or adjust how it handles storage:

• By default, Laravel stores timestamps in UTC and converts them to your APP_TIMEZONE when retrieving attributes (this is the recommended best practice for consistency across servers). If you specifically need to store timestamps in your local timezone instead, override the getDateFormat method in your model:

  protected function getDateFormat()
  {
      return 'Y-m-d H:i:s';
  }
  

4. Check Server System Timezone #

Sometimes the root issue is the server itself running on the wrong timezone.

• On Linux, check the current timezone with:

  timedatectl
  

• Set it to the correct timezone if needed:

  sudo timedatectl set-timezone Asia/Shanghai
  

• On Windows, adjust the system clock timezone through the Control Panel or Settings app.

After going through these steps, create or update a test record—your created_at and updated_at timestamps should now match your local machine's time without the 6-hour offset.

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