Advice on working out/displaying time spent in a particular state using timestamps for very large dataset

[Anonymous]

Hello everyone,

I'm really stuck on where to start with this one - hoping somebody can share some wisdom!

Basically, we have machines that run all day and flip between various states - sometimes as often as every minute. This is tracked via a timestamp and state code every time it changes - something like this:

Machine	Timestamp	State	Text
Machine1	01/04/2016 01:37:50	5	Auto Clean Mode Active
Machine1	01/04/2016 01:43:01	10	Idle Mode
Machine1	01/04/2016 01:44:01	10	Idle Mode
Machine1	01/04/2016 01:45:00	4	Printing Mode Active
Machine1	01/04/2016 01:45:04	2	Homing Mode Active
Machine1	01/04/2016 01:45:12	10	Idle Mode
Machine1	01/04/2016 01:46:00	4	Printing Mode Active
Machine1	01/04/2016 01:46:14	10	Idle Mode

I want to display this in a format that achieves the same sort of thing as this:

So eventually, I want to work out for each machine, how long per day they spent in each particular state (there are approximately 5 different states).

One of the major sticking points however is the sheer volume of data - there will be up to 200 machines and each one can have records throughout the day, changing states up to every few seconds. This will also increase in the future as more machines are added so the method chosen needs to be very performance optimised. Ideally, most of the hard work should be done in the script to ensure that user response time isn't massively affected.

Does anyone have any advice on the best way to approach this? Also, does a pie chart seem the best way to go for displaying the results?

Thank you in advance!