两个varchar类型毫秒级epoch时间列计算分秒差值的问题

两个原有写法的错误如下：

• 写法1问题：
  1. 对to_char输出的字符串直接执行算术运算，类型不匹配，无法得到正确数值结果
  2. 未将varchar类型的时间戳字段转为数值类型，减法运算存在隐式转换报错风险
  3. 逻辑冗余，不需要计算完整年月日时间后再转秒数，做了大量无效运算
• 写法2问题：
  1. 算术符号错误，原公式误把减开始时间写为加开始时间，计算结果为负且完全偏离真实差值
  2. 仅输出了分钟数值，无法直接得到mi:ss格式的结果，也未处理秒数的取余和补零逻辑
  3. 同样未处理varchar转数值的类型问题

方案1（适用所有时长场景，超过1小时分钟自动累加） #

如果需要时间差超过1小时时分钟数累加（比如1小时5分输出65:00），用算术计算方案：

SELECT 
  LPAD(FLOOR((TO_NUMBER(date_modified) - TO_NUMBER(date_added)) / 1000 / 60), 2, '0') 
  || ':' 
  || LPAD(MOD((TO_NUMBER(date_modified) - TO_NUMBER(date_added)) / 1000, 60), 2, '0') AS time_diff
FROM my_table t
WHERE t.user_name = 'John';

方案2（适用时长不超过1小时的场景） #

如果确认时间差不会超过1小时，也可以用时间间隔转换方案：

SELECT TO_CHAR(
  TO_TIMESTAMP('1970-01-01', 'yyyy-mm-dd') + NUMTODSINTERVAL((TO_NUMBER(date_modified) - TO_NUMBER(date_added))/1000, 'SECOND'),
  'mi:ss'
) AS time_diff
FROM my_table t
WHERE t.user_name = 'John';

两种写法针对示例数据都会输出47:00，符合预期。

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