The reliable way to do this is to record state changes in an event table. Then use LEAD
or LAG
functions in the DB to get intervals per state, clipping to the start of shift. Sum those intervals while grouping by state. With an index on t_stamp, your DB will calculate this blisteringly fast.
Select state, sum(event_duration) as duration
From (
Select state, nextTS - clipped as event_duration
From (
Select state
, case t_stamp < :shift_start_ts then :shift_start_ts else t_stamp end as clipped
, coalesce(lead(t_stamp) over (Order by t_stamp), current_timestamp) as nextTS
From state_events
Where t_stamp >= (Select max(t_stamp) Where t_stamp < :shift_start_ts)
) intervals
) durations
Display the final duration from the row that matches the current state.