How to convert seconds to hh:mm:ss

[JClishe]

I'm pulling in data that's formatted as seconds and I'm trying to convert it to hh:mm:ss but I can't figure it out. I've searched here and found several other threads with similar questions but even after reading the suggestions I still can't get it right.

I've created a new column that takes the original column and divides by 3600, and formatted that new column as a decimal. That works, but it's not the format that I want. When I try formatting either the new column I created, or the original column, using any of the time formats the display is never correct. 

Can anyone explain how to do this in very simple terms? I don't have much experience with PBI Desktop, which is probably obvious. 🙂 

[kunalrathi55]

A few days ago, I was experiencing the same issue. I tried to summarise my solution in an article.  

https://azureops.org/articles/show-seconds-as-hh-mm-ss-in-power-bi/

[Anonymous]

Hi, 

I have prepared a detailed article for converting Seconds to hh:mm:ss format in power bi.

https://www.powerbitalks.com/2019/12/convert-seconds-to-hhmmss-format.html

It has complete information with step by step solution.

Regards,

Kaushlendra Mishra 

www.powerbitalks.com 

[S3]

Hello Kaushlendra Mishra,
I have tried what's written in your article, but I'm getting an error, that I can't convert type record to type text

[Anonymous]

The easiest way to do this will be to make sure the Column is formatted into the data type of "Time".  You can change the data type from the ribbon under the modelling tab.  The process of doing this is to select the column in your data table (easiest from the Data View) and change the data type to "Time".  Once the data type is changed to time, you can then change the Format to "hh:mm:ss" from the dropdown directly below.

Common issues you will have here is getting Power BI to appropriately assign your value to the right level of detail (i.e. classify it as seconds rather than minutes or hours).  You have already acknowledged a desire to divide by 3600, so its likely you are on the right track.  I'm not sure why you are using 3600, but will assume it relates to the format your time data is presently in.

To clarify how you can ensure the best transition.  DateTime is stored as a decimal number, the whole number component of the value makes up the Date.  The Decimal number is the Time component, where 0 is Midnight and 1 the other midnight, thus 0.5 is midday.  1 Second, as a whole number, could be converted to 1 second in DateTime by dividing it by 24 (Hours in Day), dividing again by 60 (Minutes in Hour) and then again by 60 (Seconds in minutes).  To simplify, take your seconds as a whole number and divide by 86,400  (24 x 60 x 60)

[Militambe3171]

This soluntion does not work for me, my column is in seconds 

When i tried your solution, it does not aggregate

[JClishe]

I've changed the column data type to Time but as I indicated in my original post, it doesn't display correctly. When I use Time and select hh:mm:ss or any of the other options, I just get all zeros.

To your question of why I divided by 3600, it's because dividing seconds by that number will give me hours in decimals. This is not what I ultimately want, but it is a stop gap measure until I can figure out how to get it displayed as h:mm:ss. So instead of a field that says 5276 seconds, now I have a field that says 1.46 hours, which is much easier for me to interpret. Again, this isn't what I want, but it's better than seconds. 

[MarcelBeug]

If a Power Query solution is also OK:

let
    Source = #table(type table[Seconds = Int64.Type],{{5276}, {12675}, {76538}}),
    #"Added Custom" = Table.AddColumn(Source, "Time", each #time(0,0,0) + #duration(0,0,0,[Seconds]), type time)
in
    #"Added Custom"

[JClishe]

How would I use this?

[MarcelBeug · Accepted solution]

In the Query Editor,

open your query,

go to tab "Add Column"

choose "Custom Column"

enter column name and formula

choose OK

adjust the code by adding ", type time".

[Kerk]

Hi @MarcelBeug,

can this be modified to exclude hours, but most importantly display hundrends of seconds like mm:ss.00 ?

I am recording athletes' times, so probably this should better be expressed as "duration" instead of "time", any thoughts are wellcomed.

Thanks  

[mperrah]

text(itemWithTime, "hh:mm:ss:ff")

I believe "ff" stands for fraction of second, but this will give you hundreths of a second

[brandon061017]

What about for direct query? I'm getting an error that it can't be done without converting to import mode first, which isn't an option for me per end user needs. Is there another way to turn the number of seconds since 1970 to date time and just time/hour of the day.

[yuvaraj86]

Thanks marcel this was helpful 

[84gopi]

Hi,

I have seconds value field which calculated thru SQL, I need to know how to convert this field into hh:mm:ss format and then doing sum on the result field.

We tried thru SQL using convert and varchar function, but after converion I was unable to do SUM on the field.

Can any one help me on how to get this..

[84gopi]

How to get sum on hh:mm:ss second value field? I have field which is in seconds, when i tryied changing the data time to TIME... I am getting warning message to Fix this like....Is their any way to convert Seconds field value to HH:mm:ss format and then again doing the Sum on hh:mm:ss field in POWER BI?

I tried with SQL but still not getting it....please help