MariaDB中TIMESTAMPADD计算时间戳报错问题排查与解决咨询

我正在优化工作流，其中一项是基于给定日期计算小时级时间偏移，该部分已通过多表查询和业务逻辑实现。现在需要基于timestamp值和小时偏移量计算最终时间戳，执行以下插入SQL时出错：

insert into master_expiration_index (select mci_idx, TIMESTAMPADD(HOUR, hours_persist, ingested_time) as expiration_time from tmp_file_3 where active=1);

报错信息 #

ERROR 1292 (22007): Incorrect datetime value: '2023-03-12 02:20:15' for column `ingest`.`master_expiration_index`.`expiration_time` at row 347025

添加limit 10后查询可正常执行，现咨询以下问题：

1. 该datetime值格式正确，为何报错？
2. 如何定位引发问题的行？
3. 通用修复方案是什么？

源表结构 #

MariaDB [ingest]> describe tmp_file_3;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| mci_idx       | bigint(20) unsigned | YES  |     | NULL    |       |
| mcg_idx       | bigint(20) unsigned | YES  |     | NULL    |       |
| ingested_time | timestamp           | YES  |     | NULL    |       |
| hours_persist | int(11)             | YES  |     | NULL    |       |
| active        | tinyint(1)          | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

1. 格式正确却报错的原因 #

这个错误大概率是夏令时切换导致的无效时间。以2023-03-12 02:20:15为例，北美地区的夏令时在2023年3月12日凌晨2点会直接跳转到3点，这段02:00:00到02:59:59的时间在当地时区里不存在，属于无效时间戳。即使字符串格式正确，数据库也会判定为非法值。
另外也可能是目标表master_expiration_index的expiration_time字段类型限制：如果字段是DATE而非DATETIME/TIMESTAMP，或者时区配置与源表不一致，也会触发此类错误。

2. 定位问题行的方法 #

• 直接筛选可疑时间：利用报错中的时间值，反向查找生成该时间的原始数据：

  SELECT * FROM tmp_file_3 
  WHERE active=1 
  AND TIMESTAMPADD(HOUR, hours_persist, ingested_time) = '2023-03-12 02:20:15';
  

• 分段排查行范围：若上述查询无结果，用LIMIT+OFFSET定位到报错行附近的数据：

  SELECT mci_idx, ingested_time, hours_persist, TIMESTAMPADD(HOUR, hours_persist, ingested_time) AS expiration_time 
  FROM tmp_file_3 WHERE active=1 
  LIMIT 100 OFFSET 347000;
  

• 批量验证时间有效性：用STR_TO_DATE检测生成的时间是否合法，返回NULL的即为问题行：

  SELECT * 
  FROM tmp_file_3 
  WHERE active=1 
  AND STR_TO_DATE(TIMESTAMPADD(HOUR, hours_persist, ingested_time), '%Y-%m-%d %H:%i:%s') IS NULL;
  

3. 通用修复方案 #

• 自动修正无效时间：用TIMESTAMP函数强制转换，数据库会自动将夏令时无效时间调整为合法时间（比如跳转到夏令时后的3点）：

  INSERT INTO master_expiration_index 
  SELECT 
    mci_idx, 
    TIMESTAMP(TIMESTAMPADD(HOUR, hours_persist, ingested_time)) AS expiration_time 
  FROM tmp_file_3 
  WHERE active=1;
  

• 过滤无效数据：如果不需要这类无效时间的数据，直接筛选掉对应行：

  INSERT INTO master_expiration_index 
  SELECT 
    mci_idx, 
    TIMESTAMPADD(HOUR, hours_persist, ingested_time) AS expiration_time 
  FROM tmp_file_3 
  WHERE active=1 
  AND STR_TO_DATE(TIMESTAMPADD(HOUR, hours_persist, ingested_time), '%Y-%m-%d %H:%i:%s') IS NOT NULL;
  

• 统一时区配置：切换到无夏令时的时区（如UTC），从根源避免此类问题：

  SET time_zone = 'UTC';
  

  执行上述命令后再运行插入语句，同时建议长期统一数据库和应用的时区配置。

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