Convert minutes to Day, Hours, Minute format

saeron

Hi friends

I need to make a table widget for down nodes in a modern dashboard with this Columns:

Nodename  - DownTime Duration

I wrote this:

SELECT Caption, DetailsUrl, LastSystemUpTimePollUtc, Status, MinuteDiff(ToLocal(LastSystemUpTimePollUtc), GETDATE()) AS downduartion FROM Orion.Nodes
where status = "2"

It's works fine.

Now I need to Convert "downduartion" to this format "x Days, y Hours, z Minute"

for example 9048 Minutes must show as "6 Days, 6 Hours, 48 Minutes"

Also for 430 Minutes it must shows " 7 Hours , 10 Minutes"

Thanks

edwinhoekman

How about something like:

SELECT 
      Caption
	, DetailsUrl
	, LastSystemUpTimePollUtc
	, Status
    , CASE
        WHEN minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/1440.0 > 1
            THEN CONCAT (ROUND(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/1440,1),' Days, ',(ROUND((minutediff(LastSystemUpTimePollUtc,GETUTCDATE())-(ROUND(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/1440,1)*1440))/60,1)),' Hours, ',(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())-(ROUND(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/60,1)*60)),' minutes')
        WHEN minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/1440.0 BETWEEN 0 AND 1
            THEN CONCAT ((ROUND((minutediff(LastSystemUpTimePollUtc,GETUTCDATE())-(ROUND(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/1440,1)*1440))/60,1)),' Hours, ',(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())-(ROUND(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/60,1)*60)),' minutes')
        ElSE CONCAT ((minutediff(LastSystemUpTimePollUtc,GETUTCDATE())-(ROUND(minutediff(LastSystemUpTimePollUtc,GETUTCDATE())/60,1)*60)),' minutes')
    END AS [DownTime]
FROM Orion.Nodes
where status = "2"