Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
53 lines (50 sloc) 1.68 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Lightning Connector Status/Power Mode
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=10,11,12
[Query Metadata]
QUERY_NAME=powerlog_lightnining_connector_status
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 10,11,12]
QUERY=
SELECT
DATETIME(LIGHTNINGCONNECTOR_TIMESTAMP + SYSTEM, 'unixepoch','localtime') AS ADJUSTED_TIMESTAMP,
CASE IOACCESSORYPOWERMODE
WHEN "1" THEN "UNPLUGGED"
WHEN "3" THEN "PLUGGED IN"
END AS "IO ACCESSORY POWER MODE",
DATETIME(LIGHTNINGCONNECTOR_TIMESTAMP, 'unixepoch') AS ORIGINAL_LIGHTNINGCONNECTOR_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
LIGHTNINGCONNECTOR_ID AS "PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS TABLE ID"
FROM
(
SELECT
LIGHTNINGCONNECTOR_ID,
LIGHTNINGCONNECTOR_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
IOACCESSORYPOWERMODE,
SYSTEM
FROM
(
SELECT
PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS.TIMESTAMP AS LIGHTNINGCONNECTOR_TIMESTAMP,
IOACCESSORYPOWERMODE,
PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS.ID AS "LIGHTNINGCONNECTOR_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLBATTERYAGENT_EVENTFORWARD_LIGHTNINGCONNECTORSTATUS
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS LIGHTNINGCONNECTOR_STATE
GROUP BY
LIGHTNINGCONNECTOR_ID
)