Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
51 lines (48 sloc) 2.32 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Significant Locations - Outbound Start (Historical)
[Database Metadata]
DATABASE=Cloud.sqlite
PLATFORMS=IOS
VERSIONS=11
[Query Metadata]
QUERY_NAME=routined_cloud_visit_outbound_start
ACTIVITY=Location
KEY_TIMESTAMP=OUTBOUND START DATE
[SQL Query 11]
QUERY=
SELECT
DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTARTDATE + 978307200, 'unixepoch') AS "OUTBOUND START DATE",
DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTOPDATE + 978307200, 'unixepoch') AS "OUTBOUND STOP DATE",
ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "COORDINATES",
ZRTLEARNEDVISITMO.ZPLACE AS "PLACE ID",
ZRTLEARNEDVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT",
ZRTLEARNEDVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY",
ZRTLEARNEDVISITMO.ZCONFIDENCE AS "CONFIDENCE",
DATETIME(ZRTLEARNEDVISITMO.ZENTRYDATE + 978307200, 'unixepoch') AS "VISIT ENTRY",
DATETIME(ZRTLEARNEDVISITMO.ZEXITDATE + 978307200, 'unixepoch') AS "VISIT EXIT",
DATETIME(ZRTLEARNEDVISITMO.ZCREATIONDATE + 978307200, 'unixepoch') AS "VISIT CREATION",
DATETIME(ZRTLEARNEDVISITMO.ZEXPIRATIONDATE + 978307200, 'unixepoch') AS "VISIT EXPIRATION",
ZRTDEVICEMO.ZDEVICECLASS AS "DEVICE CLASS",
ZRTDEVICEMO.ZDEVICEMODEL AS "DEVICE MODEL",
ZRTDEVICEMO.ZDEVICENAME AS "DEVICE NAME",
DATETIME(ZRTLEARNEDPLACEMO.ZCREATIONDATE + 978307200, 'unixepoch') AS "LEARNED PLACE CREATION",
DATETIME(ZRTLEARNEDPLACEMO.ZEXPIRATIONDATE + 978307200, 'unixepoch') AS "LEARNED PLACE EXPIRATION",
DATETIME(ZRTLEARNEDPLACEMO.ZMAPITEMCREATIONDATE + 978307200, 'unixepoch') AS "MAP ITEM CREATION",
HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEMHANDLE) AS "PLACE NAME BLOB (HEX)",
HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEM) AS "PLACE GEO BLOB (HEX)",
ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE AS "LATITUDE",
ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "LONGITUDE",
ZRTLEARNEDVISITMO.Z_PK AS "ZRTLEARNEDVISITMO TABLE ID"
FROM
ZRTLEARNEDVISITMO
LEFT JOIN
ZRTDEVICEMO
ON ZRTLEARNEDVISITMO.ZDEVICE = ZRTDEVICEMO.Z_PK
LEFT JOIN
ZRTLEARNEDPLACEMO
ON ZRTLEARNEDPLACEMO.Z_PK = ZRTLEARNEDVISITMO.ZPLACE
LEFT JOIN
ZRTLEARNEDTRANSITIONMO
ON ZRTLEARNEDTRANSITIONMO.Z_PK = ZRTLEARNEDVISITMO.ZOUTBOUND
WHERE "OUTBOUND START DATE" NOT NULL