Kapacitor join mismatched time series

Hi all,

I am trying to calculate a value from two time series with very difference frequencies.
I have a steam of values of live data updating approx every second and a second stream of “shift” data coming in once every 12 hours and need to calculate a field from both.

I can’t seem to find a way to make it work.

live shift
time field_a time field_b
---------------------- ----------------------
13:56:00 1.23 06:00:00 100
13:56:02 1.56 18:00:00 200
13:56:05 2.13
13:56:06 0.98

I am picturing something similar to:

var shift = stream|from()

var live = batch|query('select "field_a" from shift')
                                    .where(live.time > shift.time & live.time < (shift.time + [12hours]))

            .as('shift', 'live')
       |eval(lambda: "shift.field_b" * "live.field_a")

can anyone point me in the right direction?