将毫秒转换为DDDD:HH:mm:ss时出现配置单元/SQL错误

Question

我有一个以毫秒为单位的输出，它太大了，无法用HH:mm:ss格式描述，我需要扩展到DDDD:HH:mm:ss。

我目前使用的代码只适用于大数：

select from_unixtime(cast(floor(2513702864/1000) as bigint), 'DDDD:HH:mm:ss');

给出: 0030:02:15:02，这是正确的

select from_unixtime(cast(floor(17259/1000) as bigint), 'DDDD:HH:mm:ss');

给出: 0001:00:00:17，这是不正确的。

select from_unixtime(cast(floor(127259/1000) as bigint), 'DDDD:HH:mm:ss');

给出: 0001:00:02:07，这也是不正确的。

在处理较小毫秒时，如何修复DDDD部分中的错误1？

Answer 1

逻辑是简单的数学。BIGINT时间戳是从Unix纪元(1970-01-01 00:00:00 UTC)经过的秒数或毫秒数。

要获取毫秒部分，请使用(ts % 1000) -在除以1000后返回提醒

要获得经过的总秒数，请使用(ts div 1000) -返回整数部分，所有其他数字将从这个数字中计算出来:天、小时、分钟、秒。

days：(ts div 1000) div 86400 -返回总秒数除以一天中的秒数后的整数部分

要获得整天计算后的剩余小时数:在天数计算((ts div 1000) % 86400)后获取提醒，并除以小时中的秒数，获取整数部分(((ts div 1000) % 86400) div 3600)

诸若此类。

演示：

with your_data as (
select 1 id, bigint(2513702864) ts union all
select 2, bigint(17259) union all
select 3,bigint(127259) union all
select 4,bigint(1272) union all
select 5,bigint(127)
)

select --format output as required. For example days:hours:minutes:seconds.millis
       concat(days,':',hours,':',minutes,':',seconds,'.',millis)
from
(
select ((ts div 1000) div 86400) days, --number of whole days
       lpad(((ts div 1000) % 86400) div 3600, 2, 0) hours, --whole hours left
       lpad((((ts div 1000) % 86400) % 3600) div 60, 2, 0) minutes, --whole minutes left
       lpad((((ts div 1000) % 86400) % 3600) % 60, 2, 0) seconds, --seconds left
       (ts % 1000) as millis
  from your_data
)s

结果：

1 29:02:15:02.864 --29 whole days, 2 hours, 15 minutes, 2 seconds, 864 millis
2 0:00:00:17.259 --17 whole seconds and 259 millis
3 0:00:02:07.259 --two whole minutes, 7 seconds and 259 millis
4 0:00:00:01.272 --one whole second and millis
5 0:00:00:00.127 --we have only milliseconds

现在，您可以看到此计算与from_unixtime返回的结果之间的差异。对于record id=1，完整的天数是29。为什么from_unixtime返回30 (对于pattern 'D')？因为29天过去了，我们是2小时15分2秒864英里在新的一天30。换句话说，from_unixtime返回时间戳的格式和查询中的计算返回间隔的格式，“一年中的某一天”和“经过的整个天数”是不同的。

希望，现在它就像一天一样清晰。

另请参阅类似问题：https://stackoverflow.com/a/57497316/2700344

如果您需要将以毫秒为单位的bigint时间戳转换为保留毫秒的字符串(yyyy-MM-dd HH:mm:ss.SSS)，请使用以下代码：

select concat(from_unixtime(ts div 1000), '.', (ts  % 1000)) as timestamp_with_millis
  from (select bigint(2513702864)  as ts) s

结果：

1970-01-30 02:15:02.864