I’m quite new to SQL and my database design is probably quite poor, however I would appreciate some help attempting to match files.
The Project: A video camera records an image file and video file when it detects motion which are then uploaded via FTP to my raspberry Pi. The files are picked up and then added to a “pending files” table:
Filename File Type (jpg/h264) Size Date (e.g.20200610) Time (e.g. 115534)
Unfortunately the timestamps are a bit variable from the camera between the video and image file. Sometimes they are separated by a second, sometimes 0 seconds, sometimes up to 7 seconds. Given how much data the camera is generating, and modelling it I’d only lose about 5% of the footage by discarding files with a difference of more than 2 seconds. Also, it seems to vary whether the image file or video file is uploaded first.
I’m struggling with the best way to identify these matched files. At the moment I’m running all the data (Ordered by date then time) through a for loop in php and comparing each file to the one before and after it, however it occasionally matches the image file to the wrong video (either earlier or later) as the files aren’t coming though as image, video, image, video etc.
Looking through previous answers I think I might need to split my table to have two (pending videos) and (pending images) and then do a Join, but this exceeds my experience in terms of the SQL query. Any help would be appreciated. Thanks!
Advertisement
Answer
How about this?
select * from (select * from PENDING_FILES where File_Type="IMAGE") a inner join (select * from PENDING_FILES where File_Type="VIDEO") b on (a.Date=b.Date and abs(a.Time-b.Time)<9999)