none
How to display duration in the format hh:mm:ss

    Question

  • 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
    Wednesday, June 03, 2009 4:14 PM

Answers

All replies

  • Use a function to get back time

    1

    :30:15 equals 3600+1800+15 = 5415 seconds

     

    select cast(DATEADD(SECOND,5415,'1/1/1900 00:00:00') as time(0))
    Result:
    1:30:15

    Wednesday, June 03, 2009 4:26 PM
  • 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
    Wednesday, June 03, 2009 4:33 PM
  • 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
    Thursday, June 04, 2009 12:30 AM
    Moderator
  • 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
    Thursday, June 04, 2009 10:44 AM
  • Hi Matt,

    Have you tried using "HHH:mm:ss" or "hhh:mm:ss" (as this is how you format a time to display more than 24 hours in Excel)?

    Frank
    Thursday, June 04, 2009 5:19 PM
  • 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
    Friday, June 05, 2009 2:26 PM
  • Hi Matt,

    What do you get if you simply subtract 30 from your number?

    Frank
    Friday, June 05, 2009 2:54 PM
  • Hi Frank,

    This doesn't seem to work unfortunately, in fact it seems to throw the figures out a bit :-(

    Matt
    Friday, June 05, 2009 3:21 PM
  • 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
    Wednesday, June 10, 2009 2:45 AM
    Moderator
  • Hi Darren,

    Thanks - I will try that and post back the results

    Matt
    Thursday, June 11, 2009 12:27 PM
  • 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
    Monday, June 15, 2009 11:41 AM
  • 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
    Monday, June 15, 2009 12:08 PM
    Moderator
  • Hi Matt,

    Maybe this: http://sqlblog.com/blogs/mosha/archive/2008/09/26/displaying-duration-values-mdx-expressions-in-format-string.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
    Wednesday, June 17, 2009 7:22 PM
  • Darren,

    Thanks for this, I understand now and what you've suggested above works exactly right. Thanks again for your patience and help!

    Matt
    Wednesday, June 24, 2009 10:02 AM
  • Hi Matt,

    Have you found a way to show the duaration in hh:mm:ss even if exceeds 24 hrs? How to do it using a Calculated measure? I am trying to show duration in minutes in hh:mm:ss format.

    Monday, June 29, 2009 7:09 AM
  • Hi mohshafi, either of the marked answers to this thread (which have appear just after the original question) should work.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Monday, June 29, 2009 8:25 AM
    Moderator
  • 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
    Monday, June 29, 2009 11:30 AM
  • 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
    Monday, June 29, 2009 11:34 AM
  • 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 re-ordered 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/displaying-duration-values-mdx-expressions-in-format-string.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
    Monday, June 29, 2009 11:47 AM
    Moderator
  • 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
    Monday, June 29, 2009 11:51 AM
  • 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
    Monday, June 29, 2009 12:30 PM
    Moderator
  • Thanks a lot Darren,
    works like a dream.
    Monday, June 29, 2009 12:49 PM
  • Hi Mosha,

    In this case, just use a format of
    [h]:mm:ss
    any you are done. No complex conditional formats needed.

    Frank

    Thursday, July 02, 2009 10:36 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
    Friday, July 03, 2009 12:57 AM
    Moderator
  • 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
    Thursday, July 09, 2009 10:02 AM