I’m building a little time tracker. Users can track different ‘Activities’. Each time they do an Activity, I create a ‘Session’, which has a start, end, and duration.
In a repeating group, I show the user all of their Activities - for a time period of their choosing - and I show the sum total duration of all of the Sessions for each activity during that time period, like this:
The problem comes when a Session started before the time period they’re looking at but ended within the time period. Obviously, I need to include the session, but really only the part of the session that is within the chosen time period.
The way I’m currently doing it (incorrectly) is that the full duration of the session is being included.
It seems I may have to forgo those pre-calculated durations and do something on the fly, but I can’t quite imagine where to begin. Any ideas?
Stop comparing things to starts and ends. Construct date ranges and use rangewise operators.
You have sessions with ranges like this:
Session’s start<-range->Session’s end
You have a time period range:
P start<-range-> P end
A session should be summed if it either :OVERLAPS P or if P :contains session (expressed alternately as session is :CONTAINED BY P
First, you must check is your sessions :OVERLAP with range P. There are two ways this can happen of course. It can overlap with P on the start side or the end side.
If it overlaps on start side, the in period session is P’s:start<-range->session’s:end. If it overlaps the end, the in period session is session’s:start<-range->P’s:end
(I’m writing in pseudo code obviously but u get the idea.)
Date intervals are similar to ranges but compute and contain the LENGTH of start to end as an amount of time.
Basically, combining these ideas wil let you find the general solution to this problem which MAY even be able to be expressed in a single (somewhat complex) expression.
(Consult reference on date ranges and intervals. See also other posts where I’ve talked about date ranges.)
Hope that helps. Sorry not on actual machine so can’t work it out exactly, but the above should help u with how to think about it!
Ok so i think i’ve solved it. Haven’t tested it extensively though.
You were right to tag this as advanced date/time calculation.
Took me about 2hrs to crack it.
Obviously a problem like this will have several solutions so this is just one of them.
Since we’re dealing with computation on a list of lists (Sessions list inside activities list), an obvious solution will have been through some javascript or scheduling API workflows on a list.
My goal was to try and avoid any of this two for obvious reasons.
Below is an image showing an example of it working. I left your implementation and created a new repeating group (in green box) so you can compare.
You can take a look at the database values on the left and results on the right.
So here is high level overview of my solution.
My trick to avoid using api workflow on list or writing javascript was to use the List Expression element from the toolbox plugin which you already had installed in your app.
The expression circled in red below is basically what is calculating the correct end and start.
I’m using the ternary operation here. Its basically a shortcut way of doing if…else condition.
The format is (condition)? true_case : false_case.
So here for each session i’m saying if the end is less than the picker end date, return the session end, else return the picker end. I do similarly for the start and substract them.
The result is a list date intervals.
Then i use an Expression element to sum these date interval values.
