BusinessObjects Board

Function to convert seconds into HH:MM:SS

building semantic layer
#1

We have several objects in the database that are returning a duration value in SECONDS. Is there a way to create a function that would pass this SECONDS value into an HH:MM:SS to be displayed at the report level.

Thanks

Jean Robert99 :canada: (BOB member since 2009-09-09)

#2

Hi Jean,

You can find a formula in this older topic:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Thank you for the formula but this formula is to be applied at the report level which we already have. I need to define an object in the universe (DB2 database) that would do this conversion.

Thanks
JR

Jean Robert99 :canada: (BOB member since 2009-09-09)

#4

Hi,

Why not taking the report level formula as a base and rewrite it using SQL functions that are supported by your DB? Then you should get a formula that could be used in a universe object definition.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#5

OK, I have define an object at the universe level that will convert the seconds into HH:MM:SS…
time(‘00:00:00’) +sum(VP.TCA11E_CALL_DET.QUEUE_TIME) second

The problem is that this will work if within 24 hours. Everything more than 24 hours will restart at 00:00:00. If I have 86402 seconds it will convert 00:00:02

Any idea

Jean Robert99 :canada: (BOB member since 2009-09-09)

#6

The mod() function will return the remainder of a division. So if you mod() with 86400 it will return the number of seconds, less the number of full days. As far as I know, most databases have this function.

Dave Rathbun :us: (BOB member since 2002-06-06)

#7

DB2 functions are similar to the function name mentioned in above link. (FLOOR/MOD )

nvragavan (BOB member since 2009-05-17)

#8

I am not sure what the MOD function will do…
If I use the following object in the universe:
time(‘00:00:00’) +sum(VP.TCA11E_CALL_DET.QUEUE_TIME) second

This wil returm 02:38:29 if VP.TCA11E_CALL_DET.QUEUE_TIME = 9509 seconds

And it will return 00:00:29 if VP.TCA11E_CALL_DET.QUEUE_TIME = 86429 seconds instead of 24:00:29

JR

Jean Robert99 :canada: (BOB member since 2009-09-09)

#9

I didn’t catch what you were asking the first time around.

When you “add” the time element you’re casting (converting) the result into a timestamp. Time stamps don’t go beyond one day.

Here’s what I found from a web search:

http://groups.google.com/group/comp.databases.ibm-db2/browse_thread/thread/2d11fbed7ea277c3

Maybe that solution might work for you, it involves creating a custom function. It also mentions that timestamps do not go beyond 24 hours, for that you need an interval instead.

Dave Rathbun :us: (BOB member since 2002-06-06)