I have a table like the below, with timestamps and values.
show t:([]time:2022.10.10D12:30:00.100000000 2022.10.10D12:30:00.200000000 2022.10.10D12:30:00.300000000 2022.10.10D12:30:00.400000000 2022.10.10D12:30:00.500000000 2022.10.10D12:30:00.600000000;values: 1 1 1 2 2 3;duration: 300 200 100 200 100 0N)
time values duration
---------------------------------------------
2022.10.10D12:30:00.100000000 1 300
2022.10.10D12:30:00.200000000 1 200
2022.10.10D12:30:00.300000000 1 100
2022.10.10D12:30:00.400000000 2 200
2022.10.10D12:30:00.500000000 2 100
2022.10.10D12:30:00.600000000 3
The third column, duration, is calculated by looking ahead at the entry where values changes, and taking the difference of the timestamps in hundredths of seconds. So, for the first entry, values changes from 1 to 2 at 12:30:00.4, so duration is 12:30:00.400-12:30:00.100 = 300. My current table only has the time and values columns.
What’s the best way to add this column to my table?
>Solution :
One possible approach:
q)update duration:{(where[x!y]sums y)-x}[time;differ values] from t
time values duration
---------------------------------------------------------
2022.10.10D12:30:00.100000000 1 0D00:00:00.300000000
2022.10.10D12:30:00.200000000 1 0D00:00:00.200000000
2022.10.10D12:30:00.300000000 1 0D00:00:00.100000000
2022.10.10D12:30:00.400000000 2 0D00:00:00.200000000
2022.10.10D12:30:00.500000000 2 0D00:00:00.100000000
2022.10.10D12:30:00.600000000 3