cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Converting milliseconds to days:hours:minutes:seconds?

Hi,

I have a sql field that returns an elapsed time value in milliseconds (i.e. days, hours, minutes, seconds)

Is there a simple way to convert that into something meaningful?

I've managed to convert it to a datetime at source by doing this which I think worked:

Example: 30871000 milliseconds gives me 01/01/1970 08:34:31

Manual calculation of that is:

30871000 / 1000 = 30,871 seconds / 60 = 514 minutes / 60 = 8.57527777 hours, so about eight and a half hours.

The problem with that though is that this is then an elapsed time since 1 Jan 1970.  Simply doing a SUM on these won't give me a total elapsed time that I can tell?

Any thoughts on how best to tackle this greatly appreciated.

Many thanks,

D.

5 REPLIES 5
New Member

From a duration column (displayed in visuals as a decimal) I'd create a measure, this measure aggregate the duration values and display it as a readable column, here the measure code:

``````Timing =
VAR dur = AVERAGE('Fact Tempi'[Durata])
VAR hours = INT ( MOD ( dur * 24 , 24 ) )
VAR minutes = INT ( MOD ( dur * 24 * 60, 60 ) )
VAR seconds = INT ( MOD( dur * 24 * 60 * 60, 60))
VAR millis  = INT ( MOD ( dur * 24 * 60 * 60 * 1000, 1000 ))

VAR hoursText =
IF (
LEN ( hours ) = 1,
CONCATENATE ( "0", hours ),
CONCATENATE ( "", hours )
)
VAR minutesText =
IF (
LEN ( minutes ) = 1,
CONCATENATE ( "0", minutes ),
CONCATENATE ( "", minutes )
)
VAR secondsText =
IF (
LEN ( seconds ) = 1,
CONCATENATE ( "0", seconds ),
CONCATENATE ( "", seconds )
)
VAR millisText =
IF (
LEN ( millis ) = 3,
CONCATENATE ( "", millis ),
IF (
LEN ( millis ) = 1,
CONCATENATE ( "00", millis ),
CONCATENATE ( "0", millis )
)
)
RETURN
hoursText & ":" & minutesText & ":" & secondsText & "." & millisText``````

Anonymous
Not applicable

ASA =
VAR durationAverage = CALCULATE(DIVIDE([Total Wait Time],[Calls Offered To Queue]))
VAR millisecondsStr = MOD(durationAverage, 1000)
VAR hoursInt = INT(DIVIDE(durationAverage, 3600000.0))
VAR hoursStr = RIGHT("00" & IF(ISBLANK(hoursInt),"",hoursInt),3)
VAR minutesInt = INT(DIVIDE(MOD(hoursInt 3600000.0), 60000.0))
VAR minutesStr = RIGHT("00" & IF(ISBLANK(minutesInt),"",minutesInt),2)
VAR secondsInt = MOD(INT(DIVIDE(MOD(minutesInt 3600000.0), 1000.0)), 60)
VAR secondsStr = RIGHT("00" & IF(ISBLANK(secondsInt),"",secondsInt),2)
RETURN hoursStr & ":" & minutesStr & ":" & secondsStr

Frequent Visitor

I came with the following measure to convert a decimal-column of milliseconds into "hh:mm:ss.mmmm":

```duration avg =
VAR durationAverage = INT(AVERAGE(MyTable[duration]))
VAR millisecondsStr = MOD(durationAverage, 1000)
VAR secondsInt = MOD(INT(DIVIDE(durationAverage, 1000.0)), 60)
VAR secondsStr = REPT("0", 2 - LEN(secondsInt)) & secondsInt
VAR minutesInt = INT(DIVIDE(durationAverage, 60000.0))
VAR minutesStr = REPT("0", 2 - LEN(minutesInt)) & minutesInt
VAR hoursInt = INT(DIVIDE(durationAverage, 3600000.0))
RETURN hoursInt & ":" & minutesStr & ":" & secondsStr & "." & millisecondsStr```

Change AVERAGE(MyTable[duration]) with your input value.

Super User

Not sure if this is what you are going for, but myself and @konstantinos wrote an article on something very similar:

http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Microsoft Employee

You can do this in M:

`#duration(0, 0, 0, numberOfMilliseconds)`

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.