求助：使用Kettle Spoon迁移MySQL数据时如何自动转换时区

Since you're reusing connections across all migration tasks, handling timezone conversion at the connection layer is smart—it keeps your code clean and avoids repetitive conversion logic. Here's a step-by-step approach tailored to your scenario:

1. Configure Source Database Connection (Europe/Rome Timezone) #

First, set the session timezone for your source database connection to Europe/Rome. This ensures MySQL returns all datetime/timestamp values converted to the Rome timezone, matching how your source data is stored.

How to set it: #

• Post-connection SQL: Immediately after establishing the connection, run this command:

  SET time_zone = 'Europe/Rome';
  

• Connection string parameters (depends on your database driver):
  • Java JDBC: Add serverTimezone=Europe/Rome to your JDBC URL:

    jdbc:mysql://source-db-host:3306/source_db?serverTimezone=Europe/Rome&useSSL=false
    

  • Python (mysql-connector): Include the time_zone parameter in your connect call:

    import mysql.connector
    source_conn = mysql.connector.connect(
        host="source-db-host",
        user="your-user",
        password="your-pass",
        database="source_db",
        time_zone="Europe/Rome"
    )
    

  • PHP PDO: Add timezone=Europe/Rome to your DSN:

    $source_conn = new PDO('mysql:host=source-db-host;dbname=source_db;timezone=Europe/Rome', 'user', 'pass');
    

2. Configure Target Database Connection (UTC Timezone) #

Next, set the session timezone for your target database connection to UTC. This tells MySQL to interpret incoming time values as UTC (or convert them to UTC if your driver passes timezone-aware dates).

How to set it: #

• Post-connection SQL: After connecting to the target DB, run:

  SET time_zone = 'UTC';
  

• Connection string parameters:
  • Java JDBC: Use serverTimezone=UTC:

    jdbc:mysql://target-db-host:3306/target_db?serverTimezone=UTC&useSSL=false
    

  • Python (mysql-connector):

    target_conn = mysql.connector.connect(
        host="target-db-host",
        user="your-user",
        password="your-pass",
        database="target_db",
        time_zone="UTC"
    )
    

  • PHP PDO:

    $target_conn = new PDO('mysql:host=target-db-host;dbname=target_db;timezone=UTC', 'user', 'pass');
    

3. Key Notes for Different Column Types #

Be aware of how MySQL handles timezone conversion for different date types:

• TIMESTAMP columns: These are stored as UTC internally. When you read from the source (Rome timezone), MySQL returns the Rome-local time; when you write to the target (UTC timezone), MySQL automatically converts the Rome time back to UTC for storage. This works seamlessly with the connection settings above.
• DATETIME columns: These store raw date/time values without timezone info. If your source DATETIME values are stored as Rome-local time, you’ll need to explicitly convert them to UTC before writing to the target. Since you want to avoid per-query logic, you can:
  • Use a view on the source database that converts DATETIME columns to UTC (e.g., SELECT CONVERT_TZ(rome_datetime, 'Europe/Rome', 'UTC') AS utc_datetime FROM source_table), then migrate from the view instead of the raw table.
  • Or, in your migration code, add a single helper function to convert timezone-aware date objects (from the source connection) to UTC before writing—this is still lightweight since it’s a one-time setup, not per-query.

Final Check #

Test with a sample date to verify:

1. Pick a date in Rome timezone (e.g., 2024-06-01 12:00:00 Rome, which is 2024-06-01 10:00:00 UTC during daylight saving).
2. Read it from the source connection (should return 12:00:00).
3. Write it to the target connection.
4. Read it back from the target (with UTC session timezone) — it should show 10:00:00.

This setup ensures all timezone conversion happens at the connection layer, keeping your migration logic focused on data transfer rather than date handling.

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