Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
dparkinson
Advocate I
Advocate I

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:

 

dateadd(millisecond, myfield %1000, dateadd(second, myfield / 1000, '19700101'))

 

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
therealfzoccara
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

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

Greg_Deckler
Super User
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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

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

You can do this in M:

#duration(0, 0, 0, numberOfMilliseconds)

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.