Calculate range of an event based on date 1

[er123]

I am writing a report that is meant to display how many hours in a particular date range a machine is running. Each time the machine is turned on it creates a new event in the db with a start time field and end time field. These times are in seconds in epoch form from 1/1/1990. I have discovered in formula writer that I can convert these times to datetime with the DateAdd function.
My next step is to figure out a way to calculate only the part of the event that falls within the date parameter. For example, if the parameter is runtime between 1/1 0700 hrs and 1/2 0700 hrs and there is a run event from 0600 hrs on 1/2 to 1200 hrs on 1/2 I would only want it to count 1 hour of run time on my report. Any ideas on a formula for this? Any help would be greatly appreciated!!

 

[lbass]

Are you using the parameter to limit the records in the selection formula?

{@enddt} >= {?start} and
{@startdt} <= {?end}

Then you would need to use a formula something like this (assuming you have converted to datetimes}:

if {@startdt} >= {?start} and
{@enddt} <= {?end} then
datediff("s",{@startdt},{@enddt}) else
if {@startdt} < {?start} and
{@enddt} <= {?end} then
datediff("s",{?start},{@enddt}) else
if {@startdt} >= {?start} and
{@enddt} > {?end} then
datediff("s",{@startdt},{?end}) else
if {@startdt} < {?start} and
{@enddt} > {?end} then
datediff("s",{?start},{?end})

Not sure what interval you wanted to use--this returns seconds.

-LB

 

[er123]

Yes I would use a start and end parameter.
Hugely helpful! Thanks very much!