时间戳

首页 > 解决方案 > 将持续时间格式 P0DT0H0M0S（ISO 8601 持续时间格式）转换为小时

sql - 将持续时间格式 P0DT0H0M0S（ISO 8601 持续时间格式）转换为小时

问题描述

我正在寻找一种将“ISO 8601 持续时间格式”（P0DT0H0M0S）转换为小时的便捷方法。

到目前为止，我想出了这个：

# Example in BigQuery
SELECT
    24 * CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(0)] AS INT64)
    + CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(1)] AS INT64)
    + (1/60) * CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(2)] AS INT64)
    + (1/3600) * CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(3)] AS INT64) AS HOURS

如您所见，我的方法是将数字分开并乘以 24、1、1/60、1/3600 以得到小时数。我可以减少代码量吗？

样本数据和期望的结果
输入：'P1DT2H3M44S' （1 天，2 小时，3 分钟，44 秒）
期望的输出：26.06222222222222 （这是小时）

标签： sqlgoogle-bigquerydurationiso8601

解决方案

试试下面的选项

select *,    
  (select sum(cast(val as int64) * weight)
   from unnest(regexp_extract_all(col, r'\d+')) val with offset
   join unnest([24, 1, 1/60, 1/3600]) weight with offset 
   using(offset)
  ) as hours
from `project.dataset.table`

如果适用于您问题中的样本数据 - 输出是

在此处输入图像描述

以上假设所有部分（天、小时、分钟、秒）都存在，即使它们为零

如果不是这种情况——我怀疑是这样，但不确定——需要对上述解决方案进行轻微调整。让我知道

推荐阅读