SQL转换毫秒为天、小时、分钟

Question

我需要将以毫秒为单位的值85605304.3587转换为0d 18h 21m格式的值。不知道如何开始，SQL中是否有类似于C#中TimeSpan的东西？

- sd_dracula

---

你使用哪个数据库管理系统？Postgres？Oracle？标准SQL中的数据类型（sql标签所指的）应该是“interval”。 - user330315

我正在使用SQL 2017。 - sd_dracula

没有名为“SQL 2017”的标准，但我猜你指的是“SQL Server 2017”。 - user330315

与 TimeSpan 相当的是 time，但它仅存储一天中的时间，而不是持续时间。这意味着您无法指定大于 24 小时的时间。TimeSpan 可以直接存储到 time 字段中。 - Panagiotis Kanavos

这个值从哪里来？它的范围是什么？为什么不直接将其存储到“时间”字段中？ - Panagiotis Kanavos

Answer 1

你可以明确地进行计算。我认为应该是：

select floor(msvalue / (1000 * 60 * 60 * 24)) as days,
       floor(msvalue / (1000 * 60 * 60)) % 24 as hours,
       floor(msvalue / (1000 * 60)) % 60 as minutes

注意：有些数据库使用mod代替%。

- Gordon Linoff

---

第三行的第一个括号使得这段代码无法运行。 - KtX2SkD

Answer 2

在 MS SQL SERVER 中，您可以使用以下代码：

with cte as (
    select cast(85605304.3587 as int) / 1000 / 60 as [min]
), cte2 as (
    select 
        cast([min] % 60 as varchar(max)) as minutes,
        cast(([min] / 60) % 24 as varchar(max)) as hours,
        cast([min] / (60 * 24) as varchar(max)) as days
    from cte
)
select concat(days, 'd ', hours, 'h ', minutes, 'm') as tm
from cte2

- Evgeny

Answer 3

使用本地的日期和时间函数，也许可以这样：

SELECT
    AsDateTime = DATEADD(MILLISECOND, 85605304, 0)
  , AsDateTime2 = DATEADD(NANOSECOND, 7 * 100, DATEADD(MICROSECOND, 358, DATEADD(MILLISECOND, 85605304, CONVERT(datetime2, CONVERT(datetime, 0)))))
-- Incorrect datetime2 approach I initially did, has some precision loss, probably due to datetime's millisecond issue with 0's, 3's, and 7.'s
--SELECT DontDoThis = DATEADD(NANOSECOND, 7 * 100, DATEADD(MICROSECOND, 358, CONVERT(datetime2, DATEADD(MILLISECOND, 85605304, 0))))

datetime 只覆盖秒后面的 3 位数字，而 datetime2 则保留了 7 位数字。也许还有其他类似日期对象的方法，但我不清楚。

- KtX2SkD