Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
64 lines (61 sloc) 1.63 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Keeping track of the timezone.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_timezone
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 9,10,11,12]
QUERY=
SELECT
DATETIME(TIMEZONE_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
TIMEZONENAME AS "TIME ZONE NAME",
COUNTRYCODE AS "COUNTRY CODE",
LOCALEID AS "LOCALE ID",
SECONDSFROMGMT / 3600 AS "SECONDS FROM GMT",
TIMEZONEISINDST AS "TIME ZONE IN DST",
TRIGGER AS "TRIGGER",
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
TIMEZONE_ID AS "PLLOCALEAGENT_EVENTFORWARD_TIMEZONE TABLE ID"
FROM
(
SELECT
TIMEZONE_ID,
TIMEZONE_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
TIMEZONENAME,
COUNTRYCODE,
LOCALEID,
SECONDSFROMGMT,
TIMEZONEISINDST,
TRIGGER,
SYSTEM
FROM
(
SELECT
PLLOCALEAGENT_EVENTFORWARD_TIMEZONE.TIMESTAMP AS TIMEZONE_TIMESTAMP,
TIMEZONENAME,
COUNTRYCODE,
LOCALEID,
SECONDSFROMGMT,
TIMEZONEISINDST,
TRIGGER,
PLLOCALEAGENT_EVENTFORWARD_TIMEZONE.ID AS "TIMEZONE_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLLOCALEAGENT_EVENTFORWARD_TIMEZONE
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS TIMEZONE_STATE
GROUP BY
TIMEZONE_ID
)