Converting a number into an HH:MM:SS time format - Oracle

system · May 29, 2003, 10:35am

I have a table in my database that has a NUMBER(12) column value representing an amount of time in seconds. For a report I’m building, I’d like to format this in an hours : minutes : seconds style.

Initially, I thought I could achieve this using the Oracle TO_DATE and TO_CHAR functions, but I couldn’t, as some of the seconds values represent an hours value of over 24 hours, and any amount of time greater than 24 hours cannot be rendered in an Oracle hh:mm:ss format.

(Times over 24 hours can only be displayed as a number of days).

So, I built this bit of SQL and created a measure object with a CHARACTER type.

to_char(trunc(sum(<seconds_column>)/3600), 'FM999999990')  || ':' || to_char(trunc(mod(sum((<seconds_column>),3600)/60), 'FM00') || ':' || to_char(mod(sum((<seconds_column>),60), 'FM00')

This now renders a numeric value as a time in hours, minutes and seconds. It took me a while, and there doesn’t seem to be anything along these lines on the forum anywhere, so I thought I’d share it. Hopefully, it may be of use to someone else.

Darren.

Darren Griffin (BOB member since 2002-07-10)

system · May 29, 2003, 5:51pm

And I’m sure it will, assuming that “someone else” remembers to search. Seriously, thanks for posting. It’s nice when someone posts a solution instead of a problem every now and then.

Dave

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

system · May 29, 2003, 9:37pm

You’ve just got a couple of spare parentheses in there …

to_char(trunc(sum(<seconds_column>)/3600), 'FM999999990')  || ':' || to_char(trunc(mod(sum(<seconds_column>),3600)/60), 'FM00') || ':' || to_char(mod(sum(<seconds_column>),60), 'FM00')

slimdave (BOB member since 2002-09-10)

system · March 29, 2004, 8:31pm

This just helped me out of a jam…thanks!

LotusSutol (BOB member since 2003-06-30)

system · March 30, 2004, 8:39am

I’ve got an extension DLL for this as all that to_char/trunc stuff drives me mad if you have to do it alot.
I’ve sent it in for inclusion onto the BOB downloads but It won’t be there for a while. Let me know if you want it. (Only tested on 5.x)
see this for basic details and e-mail me if you would like it… or wait till it appears in the downloads.

ken.gaul (BOB member since 2002-06-18)

system · March 30, 2004, 2:50pm

[quote:13879f9c71=“ken.gaul”]I’ve sent it in for inclusion onto the BOB downloads
[/quote]

I just wanted to let you know that I’ve checked the bobdownloads@forumtopics.com email address, and haven’t received the file.

BOB Downloads (BOB member since 2003-05-05)

system · March 30, 2004, 2:52pm

The instructions in the original post said codesamples@forumtopics.com will that work or do you want me to re-submit?

ken.gaul (BOB member since 2002-06-18)

system · March 30, 2004, 4:00pm

[quote:23f6f47812=“ken.gaul”]The instructions in the original post said codesamples@forumtopics.com will that work or do you want me to re-submit?
[/quote]

Ken: We changed the email address when we changed the name of the forum. Please use the newer address, thanks. 8)

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

system · March 30, 2004, 4:20pm

Consider it done! Well actually don’t consider it done it is done

ken.gaul (BOB member since 2002-06-18)

system · April 2, 2004, 1:43pm

The dll is available here. Further discussion should take place in this topic.

Thanks Ken

BOB Downloads (BOB member since 2003-05-05)

system · October 13, 2004, 5:16pm

Ken,

Thanks for the download - the DLLs work great on my PC.

I have posted the boextensions.dll and the extfunct.txt to the UserLibs directory on our 3 BO servers.

I published a report that uses FormatAsTime to Corporate Documents. When a user accesses the report on WebI, the time is formatted correctly. When using ZABO, it doesn’t work. Is there a way for ZABO installed machines to take advantage of this?

Note: Even though extfunct.txt declaring these 2 functions has been copied to the UserLibs folder on each of the 3 BO servers, a different extfunct.txt file gets copied to the users ZABO PC UserLibs directory which doesn’t have the custom functions. (Seems to copy when they refresh the report).

Do I need to have ALL ZABO users put these 2 files on their C: drive under UserLibs or is there another way? Is there somewhere else I need to store the extfunct.txt file?

Thanks much,
Pat

patwehr (BOB member since 2004-10-05)

system · October 14, 2004, 8:51am

I’m not sure I can help there. I don’t use Zabo here and I’m not sure I remember how and what it downloads. Copying the DLL to every zabo machine is an option obviously not ideal though
There might be a registry thing you can change which could point to a network drive for UserLibs locations, but that is still a per desktop fix.

Might need to post a more generic Zabo rollout question to see if anybody else knows a better way.

Sorry I can’t be more help.

ken.gaul (BOB member since 2002-06-18)

system · October 22, 2004, 9:13pm

Hi,

I am working with BO 61B AND SYBASE 12.Can you please guide me how to achieve this in sybase.

Thanks,
Viswa

viswa (BOB member since 2002-08-26)

system · November 29, 2004, 4:36pm

Ken,

thank you very much for the boextensions.dll. It works very well with BO 5.x.

I’m testing it with the new version BO 6.5, but it dosn’t seem to work.

Do you have a new dll working with BO 6.5 ??

Thank you very much,

Dolap

dolap (BOB member since 2003-02-22)

system · November 29, 2004, 5:47pm

Not yet I haven’t got 6.5, I was working on a 6.1 one but am having trouble with it. I’ll post as soon as I have something to work with. Hopefully in the next couple of weeks.

ken.gaul (BOB member since 2002-06-18)

system · December 10, 2004, 3:51pm

OK I’ve got the 6.1b dll working. I’m not sure if 6.5 has changed significantly or not. Can somebody try it for me on 6.5 I don’t it yet
boextensions.zip (19.0 KB)

ken.gaul (BOB member since 2002-06-18)

system · December 16, 2004, 3:21pm

Clearly the 6.1 version doesn’t work on 6.5. Maybe somebody could try this one and give me some feedback. I’m sorry to be so needy but I just don’t have 6.5 yet.
boextensions65.zip (19.0 KB)

ken.gaul (BOB member since 2002-06-18)

system · December 17, 2004, 3:18pm

Hi Ken!!

You’re a GENIUS!!
I tested the formatasTime function on WEBI 6.5.1 It works perfectly.

Fantastic!!

Thank You very much!

Dolap

dolap (BOB member since 2003-02-22)

system · December 17, 2004, 3:36pm

My pleasure, always glad to help. Just glad to know it works.

ken.gaul (BOB member since 2002-06-18)

system · January 6, 2005, 9:08pm

Am I missing someting?
I put the boextensions.dll and extfunct.txt in my businessobjects’ \userLib folder, but I am not seeing the two functions in the functions list.
Where is this supposed to show up?
Do I need to do something to register the .dll or let BO know about it?

I am using 6.1a.

Thanks.
Hao

browncow (BOB member since 2002-09-05)