Informix如何将datetime类型间隔值转换为秒数

适用场景 #

两个日期/时间类型字段相减得到0 00:00:01格式的INTERVAL类型值，需转换为对应总秒数，效果等价于PostgreSQL的epoch功能。

实现方法 #

1. 两个DATETIME类型直接计算 #

如果date1和date2都是DATETIME类型，直接对差值做类型转换即可：

SELECT 
  ((date1 - date2)::INTERVAL SECOND(9) TO SECOND)::INT AS total_seconds
FROM your_table;

说明：先将时间差强制转换为仅以秒为单位的INTERVAL类型，再转成整数就得到总秒数。

2. 包含DATE类型的计算 #

如果字段是仅存储日期的DATE类型，先转换为DATETIME类型再计算：

SELECT 
  ((DATETIME(date1) YEAR TO SECOND - DATETIME(date2) YEAR TO SECOND)::INTERVAL SECOND(9) TO SECOND)::INT AS total_seconds
FROM your_table;

3. 已有间隔字符串的转换 #

如果已经拿到0 00:00:01格式的间隔字符串，先转成INTERVAL类型再处理：

SELECT 
  ('0 00:00:01'::INTERVAL DAY TO SECOND::INTERVAL SECOND(9) TO SECOND)::INT AS total_seconds
FROM dual;

上述语句执行结果为1，符合预期。

注意事项 #

• 如果时间间隔超过317年（对应9位秒数的最大值），可将SECOND(9)中的数字调大，比如改为SECOND(18)即可覆盖绝大多数业务场景
• 计算结果为负数时代表date1早于date2，逻辑和PostgreSQL的epoch计算规则一致

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