Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
63 lines (60 sloc) 1.91 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Records when and which camera is in use.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_camera_state
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query]
QUERY=
SELECT
DATETIME(CAMERA_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
BUNDLEID AS BUNDLE_ID,
CASE STATE
WHEN "0" THEN "OFF"
WHEN "1" THEN "ON"
END AS STATE,
CASE CAMERA_TYPE
WHEN "2" THEN "FRONT"
WHEN "0" THEN "BACK"
END AS CAMERA_TYPE,
DATETIME(CAMERA_TIMESTAMP, 'unixepoch') AS ORIGINAL_CAMERA_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
CAMERA_ID AS "PLCAMERAAGENT_EVENTFORWARD_CAMERA TABLE ID"
FROM
(
SELECT
BUNDLEID,
CAMERA_ID,
CAMERA_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) as max_id,
SYSTEM,
CAMERA_TYPE,
STATE
FROM
(
SELECT
PLCAMERAAGENT_EVENTFORWARD_CAMERA.TIMESTAMP AS CAMERA_TIMESTAMP,
PLCAMERAAGENT_EVENTFORWARD_CAMERA.BUNDLEID,
PLCAMERAAGENT_EVENTFORWARD_CAMERA.CAMERATYPE AS "CAMERA_TYPE",
PLCAMERAAGENT_EVENTFORWARD_CAMERA.STATE,
PLCAMERAAGENT_EVENTFORWARD_CAMERA.ID as "CAMERA_ID",
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM,
BUNDLEID
FROM
PLCAMERAAGENT_EVENTFORWARD_CAMERA
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS CAMERASTATE
GROUP BY
CAMERA_ID
)