SQL Convert Milliseconds to Days, Hours, Minutes

Question

I need convert a millisecond value, 85605304.3587 to a value like 0d 18h 21m. No idea on how to start that, is there something similar to a TimeSpan in SQL like there is in C#?

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? Postgres? Oracle? The data type in standard SQL (which is what the `sql` tag refers to) would be `interval` — , Jul 17 '17 at 14:25
There is no standard named "SQL 2017" but I guess you meant "SQL **Server** 2017" — , Jul 17 '17 at 14:39
The equivalent to `TimeSpan` is `time` but it stores only the *time of day*, not a duration. That means you can't specify times greater than 24 hours. `TimeSpan` can be stored directly into a `time` field — Panagiotis Kanavos, Jul 17 '17 at 14:45
Where does this value come from? What is its range? Why not store it into a `time` field directly? — Panagiotis Kanavos, Jul 17 '17 at 14:45

Gordon Linoff · Accepted Answer · 2017-07-18T01:10:50.750

26

You can do the calculation explicitly. I think it is:

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

Note: Some databases use mod instead of %.

edited Jul 18 '17 at 01:10

answered Jul 17 '17 at 14:31

Gordon Linoff

1,242,037
58
646
786

1st bracket in 3rd line makes this unrunnable. – KtX2SkD Jul 17 '17 at 14:51

Evgeny · Answer 2 · 2017-07-17T14:51:32.617

2

In MS SQL SERVER you can use next code:

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

edited Jul 17 '17 at 14:51

answered Jul 17 '17 at 14:38

Evgeny

597
2
7
16

KtX2SkD · Answer 3 · 2017-07-17T15:19:10.680

Using native date & time functions, maybe:

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 covers only 3 digits beyond seconds, while datetime2 will maintain 7 digits. Perhaps other ways that give date-like objects exist, I wouldn't know.

SQL Convert Milliseconds to Days, Hours, Minutes

3 Answers3

Linked