To find difference of time

system · September 22, 2004, 9:19am

Hi all,

In reporter, I have 2 columns that is start_time and end_time and I want to find the time difference between these columns. Is BO has a function to find the difference of time? Please suggest.

Thanks!
May

NewbieBOB (BOB member since 2004-09-22)

system · September 22, 2004, 10:07am

I don’t think there’s a function for this in BO, at least I don’t know it.

If it’s a Date-time field in your database you could make a universe object like (oracle):

floor(((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS '

This could probably be done with the days-between functionality in BO reporter as well, as (date1-date2) will return a numeric field in days (just wouldn’t know if BO round it up) and the multiply by 246060 is just to convert it to seconds, etc.

jobjoris (BOB member since 2004-06-30)

system · September 22, 2004, 11:40am

There are quite a few different formulas/solutions for this issue- here are a few:

Follow these steps to calculate the time between two dates in "hh:mm:ss" format: 
 
1. Create a variable called <Days_into_Seconds>. This will calculate the days between and converts then into seconds:

[code]
=(DaysBetween(<BeginDate>,<EndDate>) * 24 * 60*60)
[/code]
 
2.	Create a <Start Seconds> variable for the "BeginDate":

[code]
=(ToNumber(FormatDate(<BeginDate > ,"HH"))*60) +(ToNumber(SubStr(FormatDate(<BeginDate > ,"HH:mm") ,4 ,2))) *60 +(ToNumber(SubStr(FormatDate(<BeginDate> ,"HH:mm:ss") ,7 ,2)))
[/code]
 
3.	Create a <End Seconds> variable for the "EndDate", but this time adding the <Days_into_Seconds> to this as well:

[code]
=((ToNumber(FormatDate(<EndDate > ,"HH"))*60) +(ToNumber(SubStr(FormatDate(<EndDate > ,"HH:mm") ,4 ,2))) *60)+(ToNumber(SubStr(FormatDate(<EndDate> ,"HH:mm:ss") ,7 ,2))) + <days into seconds>
[/code]

4.	Subtract them to get the difference between these two dates (call this variable <Seconds Difference>) in seconds.

[code]
=<End Seconds>-<Begin Seconds>
[/code]

5.To convert this to "hh:mm:ss" format, use the following formula:

[code]
=FormatNumber((Floor(Truncate((<Seconds Difference> / 3600) ,0))) ,"00")+":"+FormatNumber((Floor(Truncate(Mod(<Seconds Difference> , 3600) , 0) / 60) ) ,"00")+":"+FormatNumber(Floor(Mod(Mod(<Seconds Difference> , 3600) , 60) ) ,"00")
[/code]

You retrieve two date objects from your database and want to calculate the number of minutes between them.
To calculate the difference between to date objects and :

Number of day:

<Days>=DaysBetween(<Date1> ,<Date2>)

Number of Hours:

<Hours>=ToNumber(SubStr(FormatDate(<Date1> ,"HH:mm:ss") ,1 ,2))- ToNumber(SubStr(FormatDate(<Date2> ,"HH:mm:ss") ,1 ,2))

Number of Minutes:

<Min>=ToNumber(SubStr(FormatDate(<Date1> ,"HH:mm:ss") ,4 ,2))- ToNumber(SubStr(FormatDate(<Date2> ,"HH:mm:ss") ,4 ,2))

Number of Seconds:

<Sec>=ToNumber(SubStr(FormatDate(<Date1> ,"HH:mm:ss") ,7 ,2))- ToNumber(SubStr(FormatDate(<Date2> ,"HH:mm:ss") ,7 ,2))

You can display the full result:
In seconds:

=<Sec>+<Min>*60+<Hours>*3600+<Days>*86400

In minutes:

=<Min>+<Hour>*60+<Days>*1440

In text format:

= FormatNumber(<Hours>+24*<Days>  ,"0")+"Hour(s) "+ FormatNumber(<Min> ,"0")+" Minute(s) "+ FormatNumber(<Sec> ,"0")+" Second(s) "

To compare a date with the current date, you can use the functions CurrentDate() and CurrentTime():

<Hours>=ToNumber(SubStr(FormatDate(<Date1> ,"HH:mm:ss") ,1 ,2))- ToNumber(SubStr(FormatDate(CurrentTime(),"HH:mm:ss") ,1 ,2))


---

**Reporter Bloke** :uk: _(BOB member since 2004-05-20)_

system · September 22, 2004, 12:24pm

Or look here for a DLL you can install, which adds a new Business Objects Reporter function to calculate the time difference (down to the second) between two dates.

Andreas (BOB member since 2002-06-20)

system · September 23, 2004, 11:24am

Thank you very much for your suggestion.

My problem is, my two fields have only time information (06:30 for example) and when I list the value of it, it returns as a date data (12/30/1899) every records are the same. I have to use format cell to change format to hh:ss.
I tried to use formulars you suggested but I just couldn’t make it

Sadly, I have to hand in this report to my boss on Monday

May

NewbieBOB (BOB member since 2004-09-22)

system · September 23, 2004, 11:33am

Could you give an example of what’s happening when you just subtract two dates? Because I’m not quite getting it. Please also state information about the database (Oracle, DB2, SQLserver). It’s probably Access because this ‘1899-12-30’ is an Access Default I guess.

jobjoris (BOB member since 2004-06-30)

system · September 24, 2004, 2:50am

Thanks jobjoris.

Yes, it is MS Access connect via ODBC. Ok, I got 2 fields that is start_time and end_time and no date information in there.

Start_Time 06:30
End_Time 06:40

This is very simple, I want to find the difference of two data that should be 10 mins but I just couldn’t make it in BO. It alerts ‘Incorrect data type’ and when I tried to subtract it, it returns 00:00. And, when I see the list of value via variable editor, it displays ‘1899-12-30’ which is incorrect time format.

I think I misunderstand something here, if it is only time data, can we use date function in BO?

Any idea? Please help …

NewbieBOB (BOB member since 2004-09-22)

system · September 24, 2004, 5:58am

I’m not familiar with Access databases so this is hard for me too. I think the data is a date/time, hence why you get the 1899-12-30.

Can’t you check in Access what kind of field it is (or ask someone who can or if you can open the universe in designer, it will be visible in the relational model as well)?

If it’s a characterfield you could use substring (Substr) to split the parts of your time-field up like

=(Substr(<time1>,1,2)-Substr(<time2>,1,2)+":"+(Substr(<time1>,4,2)-Substr(<time2>,4,2)

in which the numbers in the function are beginning position resp. number of characters.

But first of all: check what data-type it is in your database!

jobjoris (BOB member since 2004-06-30)

system · October 4, 2004, 12:52pm

Thank you for your answer, jobjoris. Sadly, I cannot get it done

It’s on hold right now so I have 2 more weeks to sort this out :o

Cheers,
May

NewbieBOB (BOB member since 2004-09-22)

system · October 4, 2004, 2:56pm

This is the problem of formatting. Just set the format of the field to Date/Time - “h:mm:ss AM/PM”. So you are able to see the correct formatted data.


Follow these steps to calculate the time between two dates in "hh:mm:ss" format: by Reporter Bloke

will give you the required difference between them

lalitgoyal (BOB member since 2004-02-27)