I have a bucket containing metadata coming from license plate cams. All records have a tag “camid” and a field “plate” containing the numberplate.
I have been sweating for a few hours without success to come up with a querie that counts the number of unique plates/vehicles that first passes camera A with camid “camA” and then cameraB with camid “camB” within a timeframe of 5 minutes or less for the current day.
I made some progress and my querie result is now a querie with records matching the camID and different timestamp, i also used the duration function to calculate the timedifference between the 2 timestamps. Downside is that to place it in a table i have to convert to string.
Any idea on how i can filter out the records that have a positive time difference smaller or equal to 300s
Very interesting application! After your first post, I thought about it some more and felt you would need some sort of join() function would be needed and also a distinct() or unique() function (since you said you need to count the number of unique plates).
Anyhow, to filter out the records which have a positive time difference <= 300s, can you just insert a filter function, such as this?
Using unique() was nog the way forward due to the fact that a specific plate can occur more than once a day. My querie above does the thing i want it to do and make a new with table that matches each record on camA with timestampA to each record with the same plate on camB with timestampB.
However the column time_diff nu spits out data like this in string format
Anybody an idea how i can filter this so that i am only left with a positive time difference that is smaller or equal to 300 seconds?