500049 Video Annotation and Reference System : SQL Examples This page last changed on Nov 29, 2006 by brian. ## SQL Scripts Scripts that have been used with VARS can be found in the source code repository ## Examples Retrieving snap times of frame-grabs #### Inputs Fragments of the image URL #### Outputs The datetime that the image was taken #### Query Example
    SELECT
        MIN(dbo.Observation.ObservationDTG) AS SnapTime
    FROM
        dbo.VideoFrame INNER JOIN
        dbo.CameraData ON dbo.VideoFrame.id = dbo.CameraData.VideoFrameID_FK INNER JOIN
        dbo.Observation ON dbo.VideoFrame.id = dbo.Observation.VideoFrameID_FK
    WHERE
        (dbo.CameraData.StillImageURL LIKE '%Ventana%2603%00_34_19_13.jpg')
Retrieving the epoch seconds that a video-frame was recorded using dive number and tape time-code #### Inputs Dive number Tape timecode ROV name (either Tiburon or Ventana) #### Outputs The date of a video-frame in epoch seconds. This is the date that the video was recorded. #### Query Example
    SELECT
        CONVERT(bigint, DATEDIFF(ss, '01-01-1970 00:00:00', dbo.VideoFrame.RecordedDtg)) AS Epoch
    FROM
        dbo.VideoArchiveSet INNER JOIN
        dbo.VideoArchive ON dbo.VideoArchiveSet.id = dbo.VideoArchive.VideoArchiveSetID_FK INNER JOIN
        dbo.VideoFrame ON dbo.VideoArchive.id = dbo.VideoFrame.VideoArchiveID_FK INNER JOIN
        dbo.CameraPlatformDeployment ON dbo.VideoArchiveSet.id = dbo.CameraPlatformDeployment.VideoArchiveSetID_FK
    WHERE
        (dbo.CameraPlatformDeployment.SeqNumber = '2603')
        AND (dbo.VideoFrame.TapeTimeCode = '00:34:19:13')
        AND (dbo.VideoArchiveSet.PlatformName = 'Ventana')
Retrieving the URL's for framegrabs where the observation occured within the last 2 days #### Inputs None #### Outputs The (non-null) image for any observations that were created within the last 2 days #### Query Example
    SELECT
        dbo.CameraData.StillImageURL
    FROM
        dbo.CameraData INNER JOIN
        dbo.VideoFrame ON dbo.CameraData.VideoFrameID_FK = dbo.VideoFrame.id INNER JOIN
        dbo.Observation ON dbo.VideoFrame.id = dbo.Observation.VideoFrameID_FK
    WHERE
        (dbo.Observation.ObservationDTG > DATEADD([day], - 2, GETDATE())) AND
        (NOT (dbo.CameraData.StillImageURL IS NULL))
Query that returns all dive numbers (and vehicles) for any new or updated observation in the last 2 days #### Inputs None #### Outputs The vehicle and dive number for any VideoArchiveSet that has observations add or modified within the last 2 days. #### Query Example
    SELECT DISTINCT
        RovName,
        DiveNumber
    FROM
        Annotations
    WHERE
        (ObservationDate > DATEADD([day], - 2, GETDATE())) AND
        (NOT (Image IS NULL))

Document generated by Confluence on Feb 03, 2026 12:59