将分钟数转换为hh:mm格式

Question

我有一个表格中的一列，它将分钟数作为numeric(18,4)字段存储，名称为[course_access_minutes]。

这些存储的值来自黑板数据库，看起来像这样：

0.0500
0.0667
0.3667
up to 
314.0833
625.8167

我该如何将这些转换为时间格式hh:mm？我仔细研究了数据库文档，但找到的只有以下内容：course_access_minutes numeric（18,4）表示用户在此登录会话期间总共访问该课程的分钟数。我能否假定可以直接将分钟转换为小时？我认为我将任何低于1的值视为0分钟。在SQL中最好的方法是什么？感谢您的帮助。

- Dafmeister

---

你的样本数据会产生什么预期输出？ - Sean Lange

@Sean Lange 的示例数据如上所述，我想将其转换为 hh:mm 格式，例如（48:35 代表 48 小时 35 分钟）。 - Dafmeister

是的，0.0500转换为hh:mm是多少？314.0833转换为hh:mm是多少？ - Tab Alleman

是的，但主要关注大于0的值。 - Dafmeister

请解释一下当我们验证答案时，0.05对于314.0833的值是什么。 - Ajay2707

Answer 1

试试这个

SELECT CONVERT(varchar, DATEADD(s, 625.8167 * 60, 0), 108)

如果持续时间超过24小时，你可以使用以下方法：

SELECT CONVERT(varchar, CAST(1877.4501 * 60 AS int) / 3600) 
    + RIGHT(CONVERT(varchar, DATEADD(s, 1877.4501 * 60, 0), 108), 6)

- J45

---

谢谢，这真的很棒！我已经使用了CONVERT(time(0), DATEADD(s, 625.8167 * 60, 0))，因为它是时间格式。 - Dafmeister

找到了最佳答案！ - Guillermo G. Smitto

Answer 2

您可以这样使用FLOOR。

DECLARE @SampleData AS TABLE
(
    Minutes numeric(18,4)
)

INSERT INTO @SampleData
VALUES
( 0.0500),
( 1.0500),
( 30.0500),
( 80.0500),
( 314.0833),
( 625.8167)

SELECT CONCAT(floor(sd.Minutes/60),':', CASE    WHEN sd.Minutes - floor(sd.Minutes/60)*60 < 1 THEN '0'
                                                ELSE FLOOR(sd.Minutes - floor(sd.Minutes/60)*60 )
                                        END) AS hours
FROM @SampleData sd

返回

hours
0:0
0:1
0:30
1:20
5:14
10:25

- TriV

---

谢谢你的帮助，这个也行，但我选择了一行代码的答案。 - Dafmeister

@Dafmeister 是的，但是采纳的答案只能让你获得不超过24小时。 - John Cappelletti

Answer 3

WITH _Samples AS (
    SELECT CONVERT(numeric(18, 4), 0.0500) [course_access_minutes]
    UNION ALL SELECT 0.0667
    UNION ALL SELECT 0.3667
    UNION ALL SELECT 314.0833
    UNION ALL SELECT 625.8167
) 
SELECT 
    S.course_access_minutes, 

    -- split out the number
    FLOOR(S.course_access_minutes / 60) [hours], 
    FLOOR(S.course_access_minutes % 60) [minutes], 
    FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60) [seconds], 

    -- to a string
    CONVERT(varchar(10), FLOOR(S.course_access_minutes / 60)) 
    + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR(S.course_access_minutes % 60)), 2)
    + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60)), 2) [time_string], 

    -- You could consider converting to the time data type if the values will never exceed the limit
    -- time supports 00:00:00.0000000 through 23:59:59.9999999
    -- 0 through 1439.9833333 ... 23 * 60 = 1380 + 59 = 1439 + (59 / 60) = 1439.9833333 
    -- (see: https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql)
    CONVERT(time, 
        CONVERT(varchar(10), FLOOR(S.course_access_minutes / 60)) 
        + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR(S.course_access_minutes % 60)), 2)
        + ':' + RIGHT('00' + CONVERT(varchar(10), FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60)), 2) 
    ) [time]
FROM 
    _Samples S 

（进一步实现这个想法并将分数秒拆分出来并不困难。）

（得到以下结果：）

course_access_minutes  hours  minutes  seconds  time_string  time
---------------------- ------ -------- -------- ------------ ----------------
0.0500                 0      0        3        0:00:03      00:00:03.0000000
0.0667                 0      0        4        0:00:04      00:00:04.0000000
0.3667                 0      0        22       0:00:22      00:00:22.0000000
314.0833               5      14       4        5:14:04      05:14:04.0000000
625.8167               10     25       49       10:25:49     10:25:49.0000000

- Greg

Answer 4

请注意，这将类似于Greg的回答，但我想解释并简化它。

您有分钟，因此将它们除以60并向下取整（去掉小数）即可获得小时数（不包括分钟）。

如果再次获取总分钟数，并删除（mod它）向下取整的小时数 - 这需要通过乘以60进行转换 - 您就会剩下剩余的分钟数，基本上只是找出在减去那么多组六十后剩下了什么：

SELECT FLOOR(course_access_minutes / 60) as Hours,
(FLOOR(course_access_minutes) % 60) as Minutes
FROM MyTable

如果您希望以十进制形式显示分钟分数的小数部分（希望以十进制形式显示秒数），请删除FLOOR。

如果您希望以实数形式显示秒数，请保留FLOOR并使用Greg提供的方法：FLOOR((S.course_access_minutes - FLOOR(S.course_access_minutes)) * 60)来获取秒数。但是要注意括号，因为您可能会意外地将小数化的分钟向下取整并得到0，然后0*60就是零：

FLOOR(
    (
        course_access_minutes - 
        FLOOR(course_access_minutes)
    ) * 60
) as Seconds

- vapcguy