Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Convert Milliseconds to Days, Hours, Minutes

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#?

like image 594
sd_dracula Avatar asked Jul 17 '17 14:07

sd_dracula


People also ask

How do I convert a datetime to date in SQL?

You can convert a DATETIME to a DATE using the CONVERT function. The syntax for this is CONVERT (datetime, format). This shows the date only and no time.

How do you convert milliseconds to hours in Excel?

So if a millisecond 1/1000th of a second then there are 60,000 milliseconds in a minute, 3,600,000 milliseconds in an hour and 86,400,000 in a day. So all you need to do is divide the data you have in milliseconds by 86,400,000. Format the result as [h]:mm:ss and you are done.


3 Answers

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 %.

like image 172
Gordon Linoff Avatar answered Nov 08 '22 02:11

Gordon Linoff


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
like image 26
Evgeny Avatar answered Nov 08 '22 04:11

Evgeny


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.

like image 1
KtX2SkD Avatar answered Nov 08 '22 04:11

KtX2SkD