Answered by:
How to display duration in the format hh:mm:ss

Hi there,
This is probably a simple thing but I just can't figure it out. I have a column in my db which is storing a duration value as an int in seconds. I am using this in various calculations in SSAS to show AVG/SUM over time etc, but need to display it in the format hh:mm:ss to users rather than the number of seconds which is what it defaults to.
Obviously I can divide the amount by 60 to get the number of minutes, and 60 again for hours, but even then "one and a half minutes" is displayed as 1.5 instead of 00:01:30 which is what I need.
If I choose "short time" or enter "hh:mm:ss" in the Format String for the Calculation this doesn't seem to work  it will convert the value to that format but it is not the correct duration, and it certainly doesn't calculate correctly as you browse down the cube.
Has anyone come across this or have any ideas for what to try?
Thanks
Matt
Question
Answers

Thats because in a raw date time 15324 is 15324 days, if your measure is in seconds you still need to divide by 86400 (which I would be tempted to do in the DSV or as part of the ETL. But if you want to still store the full value in seconds the calc should look like the following
WITH
MEMBER measures.x as 15324
MEMBER Measures.a as
cstr(int(measures.x/86400)) + ":" + format(cdate(measures.x/86400  int(measures.x/86400)), "HH:mm:ss")
SELECT
a on 0 FROM [Adventure Works]
http://geekswithblogs.net/darrengosbell  please mark correct answers Marked as answer by Darren GosbellMVP, Moderator Monday, June 22, 2009 1:21 AM

Hi Matt,
Maybe this: http://sqlblog.com/blogs/mosha/archive/2008/09/26/displayingdurationvaluesmdxexpressionsinformatstring.aspx helps you. This is a bit similar to Darren's suggestion, and relies on the duration being in days with hours, minutes and seconds as fractions, as Darren explained.
Frank Marked as answer by Darren GosbellMVP, Moderator Monday, June 22, 2009 1:21 AM
All replies


Hi RedDennis,
Thanks for the response  I should have mentioned I'm running in a 2005 environment which doesn't have the Time datatype  will that matter for the solution above? Also, where exactly should I be using the function  as a named calculation in the DSV?
Matt 
Seconds in SQL and in SSAS are stored as the decimal portion of a floating point number.
eg. 1 = 1 day
0.5 = 12 hours
etc.
So if you take the number of seconds in a day (86400) and divide your seconds measure by that you can then use normal date formatting
with
member measures.x as cdate(90/86400), FORMAT_STRING = 'hh:nn:ss'
select
x on 0
from
[Adventure Works]
In this example I have hard coded in 90 seconds, but you could create a calculated measure and replace this with your seconds measure. The only problem I can see is that if your measure ever aggregates to more than 1 day the format string just truncates the results only showing the hh:mm:ss portion.
http://geekswithblogs.net/darrengosbell  please mark correct answers 
Hi Darren,
Thanks for this, it certainly displays the information exactly as I need. However the problem you mention is exactly what I'm getting  my measure often aggregates to more than one day (when browing the cube data for a year, for example). Do you know of any way to get it to display the correct number of hours  even if this exceeds 24?
Cheers
Matt 

Hi Frank,
Thanks for your help. I think I'm almost there with this! Combining Darren's calculation with the format string "dd:hh:mm:ss" I'm now getting the results accurately calculated and displayed correctly. ALMOST!!
The only remaining issue seems to be that the default value of the "dd:" component seems to be "30:" instead of "00:". So I'm getting the following results:
23hrs and 15 mins is displayed as "30:23:15:00" instead of "00:23:15:00".
1 day 4hrs and 20 mins is displayed as "31:04:20:00" instead of "01:04:20:00"
Do you guys have any clues as to how to get resolve this?!
Matt 


If you change the format for your measure to also show the "dd mmm yyyy" you will see that the 30 is coming in because day 0 is 30 Dec 1899.
The only way I can think of to get the display you want is to manually work out the days separate from the time.
eg.
cstr(int(measures.x)) + ":" + format(cdate(measures.x  int(measures.x)), "HH:mm:ss")
The bit before the colon formats the number of days and the bit after does the hours, minutes and seconds.
Note that the format function uses a slightly different set of formating codes to the format string in MDX.
http://geekswithblogs.net/darrengosbell  please mark correct answers 

Hi,
Unfortunately this doesn't seem to work either. What I get when trying this is the entire value in seconds displayed first, then 00:00:00 dispalyed after. For example, if my Measure returns the value 15324, this calculation displays "15324:00:00:00"
I appreciate your guys help on the issue, but shouldn't it be simpler than this? Surely it can't be an unusual requirement to want to display a duration value stored as seconds in an day/hour/min/second format?
Matt 
Thats because in a raw date time 15324 is 15324 days, if your measure is in seconds you still need to divide by 86400 (which I would be tempted to do in the DSV or as part of the ETL. But if you want to still store the full value in seconds the calc should look like the following
WITH
MEMBER measures.x as 15324
MEMBER Measures.a as
cstr(int(measures.x/86400)) + ":" + format(cdate(measures.x/86400  int(measures.x/86400)), "HH:mm:ss")
SELECT
a on 0 FROM [Adventure Works]
http://geekswithblogs.net/darrengosbell  please mark correct answers Marked as answer by Darren GosbellMVP, Moderator Monday, June 22, 2009 1:21 AM

Hi Matt,
Maybe this: http://sqlblog.com/blogs/mosha/archive/2008/09/26/displayingdurationvaluesmdxexpressionsinformatstring.aspx helps you. This is a bit similar to Darren's suggestion, and relies on the duration being in days with hours, minutes and seconds as fractions, as Darren explained.
Frank Marked as answer by Darren GosbellMVP, Moderator Monday, June 22, 2009 1:21 AM




Hi Matt,
Now, Even I am facing this issue of 30 days and 31 days. Did you succeed in resolving it? If yes, please guide me in resolving this.
Here are some sample results:
Minutes dd:hh:mm:ss d days hh:mm:ss
10080 06:00:00:00 7 days 00:00:00
67 30:01:07:00 0 days 01:07:00
4639 02:05:19:00 3 days 05:19:00
70 30:01:10:00 0 days 01:10:00
Thanks in advance 
Thanks Darren,
But I am stuck exactly at the place where Matt was having problems with 30 days and 31 days being shown instead of 0 and 1 days respectively.
He seems to have found a way out, and unfortunately, that solution is not a part of this thread. So requesting him to throw some light on that.
Thanks,
Shafi 
Thanks Darren,
But I am stuck exactly at the place where Matt was having problems with 30 days and 31 days being shown instead of 0 and 1 days respectively.
He seems to have found a way out, and unfortunately, that solution is not a part of this thread. So requesting him to throw some light on that.
Thanks,
Shafi
This problem WAS solved. The forum has reordered the answers to appear right under the orginal question.
The answer at the following link works: http://sqlblog.com/blogs/mosha/archive/2008/09/26/displayingdurationvaluesmdxexpressionsinformatstring.aspx
as does the following sample, which formats 15324 seconds in dd hh:mm:ss format
WITH
MEMBER measures.x as 15324
MEMBER Measures.a as
cstr(int(measures.x/86400)) + ":" + format(cdate(measures.x/86400  int(measures.x/86400)), "HH:mm:ss")
SELECT
Measures.a on 0 FROM [Adventure Works]
http://geekswithblogs.net/darrengosbell  please mark correct answers 

Thanks a lot Darren.
Now, If I want to see it it as 28:10:00 intead of 1 day 04:10:00?
How can we achieve this? My client seems to like that format much better.
Thanks in advance,
Mohsha
One possible method is just to multiply the days by 24 and add it to the hours portion (see measure b below)
WITH
MEMBER
measures.x as 153240
MEMBER
Measures.a as
cstr(int(measures.x/86400)) + ":" + format(cdate(measures.x/86400  int(measures.x/86400)), "HH:mm:ss")
MEMBER
Measures.b as
cstr((int(measures.x/86400) * 24) + CInt(format(cdate(measures.x/86400), "HH")) ) + format(cdate(measures.x/86400), ":mm:ss")
SELECT
{Measures.a,Measures.b}
on 0 FROM [Adventure Works]
http://geekswithblogs.net/darrengosbell  please mark correct answers Proposed as answer by mohshafi Monday, June 29, 2009 12:53 PM



Hi Mosha,
In this case, just use a format of
[h]:mm:ss
any you are done. No complex conditional formats needed.
Frank
Hi Frank, Can you post an example of what you are talking about? In my tests, any format like this does not work once the timespan is larger than 24 hours (it just truncates the days and returns the remaining hours)
http://geekswithblogs.net/darrengosbell  please mark correct answers 
Hi Darren,
You are right. This is the way to use this format times greater than 24 hours in Excel, and its eems AS do not understand it.
Actually, I used this type of formatting in Excel 2003 Pivot Table Services, as these do not seem to take care of the format delivered by AS.
So the only viable solution would be yours.
Sorry for mixing things up.
Frank