PySpark同步MySQL至RedShift遇时区错误及时间差问题求助

1. 写入报错的根源 #

HOUR_OF_DAY: 0 -> 1 错误是时区转换时的时间冲突导致的：America/Sao Paulo时区存在夏令时切换规则，当UTC时间落在巴西时区的夏令时调整窗口（比如春季调快1小时会出现某个小时“消失”，秋季调慢1小时会出现某个小时“重复”），Java驱动在转换时区时会抛出该异常。你设置serverTimezone=UTC让MySQL驱动以UTC读取数据，规避了转换到巴西时区时的冲突，所以报错消失，但数据的时区处理逻辑仍未对齐。

2. 数据时差的核心问题 #

RedShift的timestamp类型不带时区信息，而timestamptz（timestamp with time zone）会存储UTC时间，但查询时会根据会话/用户/集群时区转换显示。你的问题出在：

• 从MySQL读取的是UTC时间，写入RedShift时如果存为timestamp类型，RedShift会把这个值当成当前会话时区（America/Sao Paulo）的本地时间存储，而非UTC。
• 若写入的是timestamptz，但查询时会话时区为巴西时区，系统会自动把UTC时间转换为巴西时间显示，导致和原UTC数据差3小时（巴西标准时间为UTC-3）。

3. 修改用户时区不生效的解决方法 #

ALTER USER myuser SET TIMEZONE TO default 未生效，原因是：

• default 对应RedShift集群的默认时区，而你的集群默认时区就是America/Sao Paulo，所以修改后无变化。
• 已存在的会话不会应用新的用户时区设置，需重新断开并连接RedShift会话才能生效。

正确的修改方式：

• 若想让用户会话默认使用UTC时区，直接执行：

  ALTER USER myuser SET TIMEZONE TO 'UTC';
  

• 执行后关闭当前SQL客户端连接，重新登录，再执行SHOW timezone，结果应该显示UTC。

4. 彻底解决数据时差的方案 #

方案A：确保写入和查询的时区统一为UTC #

1. RedShift端：将用户时区设为UTC（按上述步骤），或查询时显式指定时区：

   -- 将timestamptz类型的UTC时间以UTC格式显示
   SELECT my_timestamp_column AT TIME ZONE 'UTC' FROM my_table;
   -- 将timestamp类型的值视为UTC时间，转换为UTC格式显示
   SELECT my_timestamp_column::timestamptz AT TIME ZONE 'UTC' FROM my_table;
   

2. 写入端：确保写入RedShift时数据以UTC时间写入，若使用timestamptz类型，驱动会自动处理时区转换；若使用timestamp类型，要保证写入的就是UTC时间，且查询时明确以UTC解析。

方案B：写入时转换为RedShift集群时区 #

如果业务需要以America/Sao Paulo时区存储数据，在从MySQL读取数据后，将UTC时间转换为America/Sao Paulo时区的时间，再写入RedShift的timestamp类型字段。可在Java代码中完成转换：

// 示例：将UTC时间转换为America/Sao Paulo时区
ZonedDateTime utcTime = ZonedDateTime.ofInstant(mySqlTimestamp.toInstant(), ZoneId.of("UTC"));
ZonedDateTime saoPauloTime = utcTime.withZoneSameInstant(ZoneId.of("America/Sao Paulo"));
// 写入RedShift时使用saoPauloTime对应的时间戳

方案C：修改RedShift集群时区（谨慎操作） #

如果整个集群都需要使用UTC时区，可通过AWS控制台或CLI修改RedShift集群的时区。注意：

• 集群时区修改后需重启集群才能生效。
• 已存储的timestamp类型数据不会自动转换，需手动处理（因为timestamp不带时区，集群时区修改后，这些数据会被视为新时区的本地时间）。

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