Time Range Data Start and End Date

influxql
time-series
#1

My application has three large data sets including data from a large fleet of sensors, second is transaction data, and the last it labor or shift data. So far everything has worked out for the sensor data and transaction data, but I’m struggling to see if there’s a fit for the labor data. I have full control over the format of the inbound data, but basically I need to store employee shift data and then retrieve the aggregate number of staff hours worked within a certain time bin (1h, 1d, etc).
I have the date of the shift, the sequence number of the shift part, the start and end of the particular shift part and the employee id. I’ve messed around with some attempts at using elapsed and difference. I’ve tried separating the start and end of the shift into different records, but so far can’t figure anything out. The output of the query should basically be an aggregate shift duration (in hours, minutes, etc) grouped by time (1h, 1d, etc). *One important challenge is that shifts aren’t normalized to the time bin I’ll be requesting data for. So a shift may start at 10:10am and end at 11:15am. This shift should contribute 50 minutes to a 10am time bin and 15 minutes to the 11:00am time bin. My guess is that this simply isn’t a fit for a TSDB, but if I’m missing something any thoughts would be greatly appreciated.