时间戳到自定义持续时间

Question

我有两个时间戳，我可以使用TIMESTAMP_DIFF得到两个的差，但我只能得到天、小时、分钟等的差异。我想要的是自定义格式中两个的差异。例如">3D：5H：23M：08S"或"03：05：23：08"或">3天5小时23分8秒"。

这可能与 BigQuery Standard SQL 一起使用吗？

Answer 1

下面用于 BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
SELECT TIMESTAMP '2020-02-13 13:28:50.532153 UTC' start, TIMESTAMP '2020-03-16 18:51:58.532153 UTC' finish UNION ALL
SELECT TIMESTAMP '2020-02-13 13:28:50.532153 UTC', TIMESTAMP '2020-02-16 18:51:58.532153 UTC'
)
SELECT -- start, finish, 
FORMAT('%sD:%s', x, FORMAT_TIMESTAMP('%HH:%MM:%SS', y)) custom_format1,
FORMAT('%s:%s', x, FORMAT_TIMESTAMP('%H:%M:%S', y)) custom_format2,
FORMAT('%sdays ,%s', x, FORMAT_TIMESTAMP('%Hhours, %Mminutes, %Sseconds', y)) custom_format3
FROM `project.dataset.table`,
UNNEST([CAST(TIMESTAMP_DIFF(finish, start, DAY) AS STRING)]) x,
UNNEST([TIMESTAMP_SECONDS(TIMESTAMP_DIFF(finish, start, SECOND))]) y

带输出

Row custom_format1  custom_format2  custom_format3   
1   32D:05H:23M:08S 32:05:23:08     32days ,05hours, 23minutes, 08seconds    
2   3D:05H:23M:08S  3:05:23:08      3days ,05hours, 23minutes, 08seconds    

Answer 2

您可以将差异转换为时间戳，然后随心所欲地使用format_timestamp()：

select TIMESTAMP_MILLIS(timestamp_diff(ts1, t23, millisecond))

这给出了 1970 年代的时间戳值(可能(，但您可能只对天、小时和较小的单位感兴趣。