convert seconds to days:hours:minutes

building semantic layer
1

In designer I have a duration time in seconds, I need to convert that to dd:hh:mm format who can help me to handle this :crazy_face:
It’s urgency!!! Thanks

funny (BOB member since 2008-09-24)

2

Hi,

Are you looking for a universe-level solution or for a report-level solution? If the former then what is the database that you use?

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

3

Hi I have both a solution (hopefully) and a further questions…

We have just done something similar here except we just used hours minutes and seconds.
If you do it on the universe side it needs some sort of cast or convert function which will depend on the underlying database (as stated below) we use:
right(‘00’+CONVERT(VARCHAR,( seconds) / 3600),2) + ‘:’ + RIGHT(‘00’+CONVERT(VARCHAR,(CONVERT(VARCHAR,seconds % 3600) / 60)),2) + ‘:’ +RIGHT(‘00’ + CONVERT(VARCHAR,seconds % 60),2)

where seconds is the variable containing the seconds in int form and the result will be a character string of the format “00:00:00” for hours minutes and seconds.

If you do it on the report side use:

=FormatNumber(Floor(seconds/3600) ,“00”) & “:” &
FormatNumber(Floor(Mod(seconds ,3600)/60) ,“00”) & “:” &
FormatNumber(Mod(Mod(seconds ,3600) ,60) ,“00”)

again you get a string “00:00:00”

Bu-ut … the problem I encountered was that the users want these values summed and averaged on the report and this can’t be done in the universe as once you convert them they become characters. So it became a toss up of where to implement the functionality. Some of it can be done in the universe but a lot of it needs to be done in the report.

I was hoping there would be a solution that would be able to convert the integer to a time field and then do calculations on it that way - till now though I can’t find anything to do that so if anyone knows how I would be EXTREMELY grateful as I have about 20 reports each with at least 10 variables and totals and averages that I don’t really want to do them manually!

Thanks

SarahG :australia: (BOB member since 2005-08-22)

4

Hi, i know this old post but i have same requirements.

I’m able to convert seconds to HH:MM:SS in webi but it is giving me error in universe. If possible, can you please provide the exact formula with object name included?

Thanks!

moons :us: (BOB member since 2007-11-08)

5

What error it is giving ??

If you are getting seconds value from the database then you can create a universe object as below:

RIGHT('00'+CONVERT(VARCHAR,(Table.secondscolumn) / 3600),2) + ':' + RIGHT('00'+CONVERT(VARCHAR,(CONVERT(VARCHAR, Table.secondscolumn % 3600) / 60)),2) + ':' +RIGHT('00' + CONVERT(VARCHAR, Table.secondscolumn % 60),2)

Also, you can substitute another object that gives you seconds at the universe level for Table.secondscolumn

BO_Chief :us: (BOB member since 2004-06-06)

6

Hi,

What formula have you tried?

And what database do you use?

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

7

I tried this in the universe and the error is “Parse failed; Exception: DBD, ORA-00936: missing expression”

moons :us: (BOB member since 2007-11-08)

8

I tried this formula
RIGHT(‘00’+CONVERT(VARCHAR,(tablename.columnname) / 3600),2) + ‘:’ + RIGHT(‘00’+CONVERT(VARCHAR,(CONVERT(VARCHAR, (tablename.columnname % 3600) / 60)),2) + ‘:’ +RIGHT(‘00’ + CONVERT(VARCHAR, (tablename.columnname % 60),2)

My system is BO XIR3.1, oracle 10g.

moons :us: (BOB member since 2007-11-08)

9

Hi,

The tablename.columnname column - what data type is it? Is it a number or a string? Can you post few examples of data in this column?

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

10

the datatype is number with no decimal.

moons :us: (BOB member since 2007-11-08)

11

Hi,

If the numbers are smaller than 86400 (which is the number of seconds in 1 single day) then this formula should give you what you need:

TO_CHAR ( TRUNC(SYSDATE) + tablename.columnname/86400 , 'HH24:MM:SS')

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

12

Hi Marek
Do you have a SQL equivalent of this sentance

Thanks

SarahG :australia: (BOB member since 2005-08-22)

13

Hi Sarah,

The syntax is a SQL syntax. It’s valid for Oracle.

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

14

Sorry I should have been more specific and said MS SQL :slight_smile:

SarahG :australia: (BOB member since 2005-08-22)

15

Thanks Marek!

I tried that but its not giving correct answer, for ex; I have 2,269 seconds. after using your formula it gives me 00:08:49. but the answer i’m looking for is 00:37:49. Any thoughts?

moons :us: (BOB member since 2007-11-08)

16

Hi,

If the tablename.columnname column contains the number of seconds, then the formula should have been this:

TO_CHAR ( TRUNC(SYSDATE) + tablename.columnname , 'HH24:MM:SS')

Did it work?

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

17

Hi Sarah,

Try if something like this works:

CONVERT(varchar, DATEADD(ss, tablename.numberofsecondscolumn, DATEADD(dd, DATEDIFF(dd,0, GetDate()), 0)), 108)

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

18

Thanks - I’ll let you know how it goes!!!

SarahG :australia: (BOB member since 2005-08-22)

19

I’m still getting incorrect result using this formula. Thanks!

moons :us: (BOB member since 2007-11-08)

20

Hi,

Can you provide the number as an input (the number of seconds) and the output of the above formula that you are getting?

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