Hmm, 500k rows is not a lot. I'd just (at least as a start) write a python function that takes one row and handles it (the straightforward way would be to iterate over all of the buckets and for each, calculate the intersection between the bucket and the interval), and `.apply` that function over your column. This is a very inefficient way to do stuff in pandas, but it should be able to handle 500k rows in a few seconds or minutes, and it isn't obvious to me how to do this particular operation in the "correct" (vectorized) fashion.
Sorry, just trying to get my head around this - So would it be something along the lines of (ignoring the days for the time being), if start time > bucket start time, start time, and if end time < bucket end time, use end time, then calculate (sort of)?