如何格式化Oracle日期差查询结果并设置列别名

核心需求说明 #

你需要将两个日期相减得到的原始差值，格式化输出为X days, X h, X min的易读形式，同时为计算列设置自定义别名。

实现方案 #

方案1：数值拆解法（写法简单，无需子查询） #

直接对日期差值做数值运算拆分各时间单位，SQL示例如下：

SELECT 
  r.created_date,
  r.updated_date,
  -- 分别计算天、时、分后拼接
  TRUNC(r.updated_date - r.created_date) || ' days, ' ||
  TRUNC(MOD((r.updated_date - r.created_date)*24, 24)) || ' h, ' ||
  TRUNC(MOD((r.updated_date - r.created_date)*24*60, 60)) || ' min' AS time_diff
FROM orders r;

逻辑说明：

• Oracle中两个日期相减的结果单位为天，整数部分为完整天数，小数部分为不足一天的时长
• TRUNC(差值)取整数部分得到天数
• 差值乘以24得到总小时数，用MOD()取余24得到不足一天的小时数
• 差值乘以1440（24*60）得到总分钟数，用MOD()取余60得到不足一小时的分钟数

方案2：INTERVAL类型处理法（规范严谨，适合复杂时间提取场景） #

先将差值转为INTERVAL类型，再用内置函数提取各时间单位，SQL示例如下：

SELECT 
  r.created_date,
  r.updated_date,
  EXTRACT(DAY FROM diff) || ' days, ' ||
  EXTRACT(HOUR FROM diff) || ' h, ' ||
  EXTRACT(MINUTE FROM diff) || ' min' AS time_diff
FROM (
  SELECT 
    r.*,
    NUMTODSINTERVAL(r.updated_date - r.created_date, 'DAY') AS diff
  FROM orders r
) r;

逻辑说明：

• NUMTODSINTERVAL()函数将天数差值转换为INTERVAL DAY TO SECOND标准时间间隔类型
• 用EXTRACT()函数分别提取间隔中的天、小时、分钟字段

别名设置说明 #

上述示例中AS time_diff就是为时间差列设置别名，规则如下：

• 别名无特殊字符/空格时，AS关键字可以省略，直接写列 别名即可
• 如果需要使用中文别名或者带空格的别名，用双引号包裹别名即可，例如AS "时间差值"

你提供的示例数据用以上两种方案计算，输出结果均为19 days, 11 h, 20 min，符合预期效果。

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