3

I am having a hard time trying to figure out the best way to report on and calculate layered time data.

The problem:

A person can sign in multiple times, during this sign in there are multiple statuses a person can be a part of. These statuses can be different per sign in per person. I am trying to figure out the best way to calculate time spent in each status as one whole session flattened together.

billable calculations

Let L1 be login 1 for person 1, let L2 be login 2. Essentially I am trying to calculate B or Billable status.

Assuming they were only ever in a Billable status on both logins then the bottom line would be the time spent in Billable.

Current solution

We store a session in our Postgres database and every 30 minutes go back and flatten the sessions into one based on start/end time and figure out time in each status. This works great for reporting that is based at least 30 minutes back, but not for real time reporting.

My tools:

I am trying to save each flattened second in each status per person to a Cassandra table. What would such a table look like? Could doing these calculations be a purely database operation?

Anyone got any ideas on how to flatten this data in C* so we only get the seconds each person has spent in each status regardless of how many logins were reporting that status?

MetaFight
  • 11,549
  • 3
  • 44
  • 75
Jared Mackey
  • 163
  • 5
  • Do you have any statuses that are more complex than "if any logins are currently in that state, then that state is current"? If not, I think a "semaphore" concept would work (I don't know your tools, which is why I'm not answering this): atomically increasing a count for the status when the login starts that status and atomically decrementing the count when the login stops being in that status can be used if you can ensure the stopping always gets recorded, even with "unexpected" logouts (e.g. power failure). Obviously, they're in that status whenever the count is positive. – Mark Hurd Feb 24 '16 at 01:36

0 Answers0