sql - 如何在 sql 中将浮点值转换为时间并获取其总和？

Question

我在 TotalHours 表中有一列是 float 类型。

值是

• 3.4
• 1.3
• 4.2

小时列遵循的格式类似于 hours.min

我想得到总和

我正在使用的查询是

select sum(TotalHours) as hours from workdone where userid = 3

所以它返回 8.9 这是错误的，首先我需要将它转换为小时然后计算总和，但我被卡住了。

如何将这些值转换为小时数并求和？

已编辑:总和值将再次采用浮点格式。例如，求和结果应该是 9.3

Answer 1

这不是存储持续时间的好方法。存储小数秒、分钟、小时是可以的，但它应该以 10 为底，即 1 小时 30 分钟，应该是 1.5 小时或 90 分钟，而不是 1.3 - 如果有人将 1.7 插入到你的表中怎么办？添加/减去以秒/分钟存储的数据并根据需要重新格式化也更加简单。

无论如何，您可以使用一些相当简单的逻辑进行转换。小时部分只是小数点前的部分，即数字的整数部分，这可以通过向下舍入您的数字来获得 - 例如FLOOR(1.3) = 1 小时。然后要获得分钟数，您需要提取数字的小数部分并乘以 100 - 例如从 1.3 中提取 0.3 并将其乘以 100 得到 30 分钟。

SELECT  [Hours] = FLOOR(n),
        [Minutes] = (n - FLOOR(n)) * 100,
        TotalMinutes = (FLOOR(n) * 60) + (n - FLOOR(n)) * 100,
        SummedMinutes = SUM((FLOOR(n) * 60) + (n - FLOOR(n)) * 100) OVER(),
        SummedHours = SUM((FLOOR(n) * 60) + (n - FLOOR(n)) * 100) OVER() / 60.0
FROM    (VALUES (3.4), (1.3), (4.2)) t (N);

然后，如果您需要将 9.5 小时转换回 9.3(同样，这是表示 9 小时 30 分钟的糟糕方式)，您需要反转上述逻辑，即总小时数 +(分钟/100)

SELECT  [Hours] = CAST(FLOOR(SummedMinutes / 60) AS FLOAT),
        [Minutes] = SummedMinutes % 60,
        OddFormat = CAST(FLOOR(SummedMinutes / 60) AS FLOAT) + (SummedMinutes % 60 / 100)
FROM    (   SELECT  SummedMinutes = SUM((FLOOR(n) * 60) + (n - FLOOR(n)) * 100)
            FROM    (VALUES (3.4), (1.3), (4.2)) t (N)
        ) AS t;

Example on SQL Fiddle