================================================================================ PI5HOME DATABASE SCHEMA + TIME REPORT ================================================================================ Aikavyöhyke : Europe/Helsinki Esitysmuoto : YYYY.MM.DD HH:MM Alkuperäinen aikaleima näytetään aina ennen muunnosta. TIETOKANNAT ---------------- ruuvi.sqlite3 weather.sqlite3 zigbee.sqlite3 ################################################################################ TIETOKANTA: ruuvi.sqlite3 ################################################################################ TAULUT name ---------------- measurements moon_events rv_names sun_daily sun_location weather_artukainen_measurements weather_artukainen_raw ================================================================================ TABLE: measurements ================================================================================ name sql --------- -------------------------------------- name sql ------------ ------------------------------------------------- measurements CREATE TABLE measurements ( id INTEGER PRIMARY KEY AUTOINCREMENT, received_utc TEXT NOT NULL, topic TEXT NOT NULL, gateway_mac TEXT, sensor_mac TEXT, rssi REAL, timestamp REAL, temperature REAL, humidity REAL, pressure REAL, voltage REAL, tx_power REAL, acceleration_x REAL, acceleration_y REAL, acceleration_z REAL, movement_counter INTEGER, measurement_sequence INTEGER, raw_data TEXT ) PRAGMA table_info(measurements); " cid name type notnull dflt_value pk --- -------------------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 received_utc TEXT 1 0 2 topic TEXT 1 0 3 gateway_mac TEXT 0 0 4 sensor_mac TEXT 0 0 5 rssi REAL 0 0 6 timestamp REAL 0 0 7 temperature REAL 0 0 8 humidity REAL 0 0 9 pressure REAL 0 0 10 voltage REAL 0 0 11 tx_power REAL 0 0 12 acceleration_x REAL 0 0 13 acceleration_y REAL 0 0 14 acceleration_z REAL 0 0 15 movement_counter INTEGER 0 0 16 measurement_sequence INTEGER 0 0 17 raw_data TEXT 0 0 " INDEXES: seq name unique origin partial --- ---------------------------- ------ ------ ------- 0 idx_measurements_sensor_time 0 c 0 FOREIGN KEYS: -------------------------------------------------------------------------------- AIKAKENTTÄ: received_utc -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18T07:06:55.830249+00:00 Tunnistettu: ISO-8601 text Viimeisin alkuperäinen : 2026-08-18T07:06:55.830249+00:00 SQLite/UTC : 2026-08-18 07:06:55 SUOMEN AIKA : 2026.08.18 10:06 VIIMEISIN AIKA SQL: SELECT "received_utc" AS alkuperainen_timestamp, datetime("received_utc") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("received_utc"), 'localtime')) AS suomen_aika FROM "measurements" WHERE "received_utc" IS NOT NULL ORDER BY datetime("received_utc") DESC LIMIT 1; ================================================================================ TABLE: moon_events ================================================================================ name sql --------- -------------------------------------- PRAGMA table_info(moon_events); " " INDEXES: FOREIGN KEYS: ================================================================================ TABLE: rv_names ================================================================================ name sql --------- -------------------------------------- PRAGMA table_info(rv_names); " " INDEXES: FOREIGN KEYS: ================================================================================ TABLE: sun_daily ================================================================================ name sql --------- -------------------------------------- PRAGMA table_info(sun_daily); " " INDEXES: FOREIGN KEYS: ================================================================================ TABLE: sun_location ================================================================================ name sql --------- -------------------------------------- PRAGMA table_info(sun_location); " " INDEXES: FOREIGN KEYS: ================================================================================ TABLE: weather_artukainen_measurements ================================================================================ name sql --------- -------------------------------------- PRAGMA table_info(weather_artukainen_measurements); " " INDEXES: FOREIGN KEYS: ================================================================================ TABLE: weather_artukainen_raw ================================================================================ name sql --------- -------------------------------------- PRAGMA table_info(weather_artukainen_raw); " " INDEXES: FOREIGN KEYS: ################################################################################ TIETOKANTA: weather.sqlite3 ################################################################################ TAULUT name ---------------- weather_aviationweather_measurements weather_aviationweather_raw weather_description weather_fmi_artukainen_measurements weather_fmi_artukainen_raw weather_metar_clouds weather_metar_stations weather_metar_unknown weather_utu_measurements weather_utu_raw ================================================================================ TABLE: weather_aviationweather_measurements ================================================================================ name sql --------- -------------------------------------- name sql ------------------------------------ ------------------------------------------------------- weather_aviationweather_measurements CREATE TABLE weather_aviationweather_measurements ( measure_id INTEGER PRIMARY KEY, station TEXT NOT NULL, event_time TEXT NOT NULL, auto INTEGER NOT NULL DEFAULT 0, cavok INTEGER NOT NULL DEFAULT 0, wind_direction REAL, wind_speed REAL, wind_gust REAL, wind_direction_min REAL, wind_direction_max REAL, visibility REAL, weather TEXT, temperature REAL, dew_point REAL, pressure REAL, cloud_status TEXT, trend TEXT, trend_data TEXT, source_raw_id INTEGER NOT NULL, FOREIGN KEY (source_raw_id) REFERENCES weather_aviationweather_raw(raw_id) ) PRAGMA table_info(weather_aviationweather_measurements); " cid name type notnull dflt_value pk --- ------------------ ------- ------- ---------- -- 0 measure_id INTEGER 0 1 1 station TEXT 1 0 2 event_time TEXT 1 0 3 auto INTEGER 1 0 0 4 cavok INTEGER 1 0 0 5 wind_direction REAL 0 0 6 wind_speed REAL 0 0 7 wind_gust REAL 0 0 8 wind_direction_min REAL 0 0 9 wind_direction_max REAL 0 0 10 visibility REAL 0 0 11 weather TEXT 0 0 12 temperature REAL 0 0 13 dew_point REAL 0 0 14 pressure REAL 0 0 15 cloud_status TEXT 0 0 16 trend TEXT 0 0 17 trend_data TEXT 0 0 18 source_raw_id INTEGER 1 0 " INDEXES: seq name unique origin partial --- ------------------------------------------------ ------ ------ ------- 0 idx_weather_aviationweather_measurements_raw 0 c 0 1 idx_weather_aviationweather_measurements_event 0 c 0 2 idx_weather_aviationweather_measurements_station 0 c 0 FOREIGN KEYS: id seq table from to on_update on_delete match -- --- --------------------------- ------------- ------ --------- --------- ----- 0 0 weather_aviationweather_raw source_raw_id raw_id NO ACTION NO ACTION NONE ================================================================================ TABLE: weather_aviationweather_raw ================================================================================ name sql --------- -------------------------------------- name sql --------------------------- -------------------------------------------- weather_aviationweather_raw CREATE TABLE weather_aviationweather_raw ( raw_id INTEGER PRIMARY KEY, received_at TEXT NOT NULL, station TEXT NOT NULL, raw_data TEXT NOT NULL, parsed INTEGER NOT NULL DEFAULT 0, parse_error TEXT ) PRAGMA table_info(weather_aviationweather_raw); " cid name type notnull dflt_value pk --- ----------- ------- ------- ---------- -- 0 raw_id INTEGER 0 1 1 received_at TEXT 1 0 2 station TEXT 1 0 3 raw_data TEXT 1 0 4 parsed INTEGER 1 0 0 5 parse_error TEXT 0 0 " INDEXES: seq name unique origin partial --- -------------------------------------- ------ ------ ------- 0 idx_weather_aviationweather_raw_unique 1 c 0 1 idx_aviationweather_raw_received 0 c 0 2 idx_aviationweather_raw_station 0 c 0 FOREIGN KEYS: -------------------------------------------------------------------------------- AIKAKENTTÄ: received_at -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18 06:52:32 Tunnistettu: TEXT Viimeisin alkuperäinen : 2026-08-18 06:52:32 SQLite/UTC : 2026-08-18 06:52:32 SUOMEN AIKA : 2026.08.18 09:52 VIIMEISIN AIKA SQL: SELECT "received_at" AS alkuperainen_timestamp, datetime("received_at") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("received_at"), 'localtime')) AS suomen_aika FROM "weather_aviationweather_raw" WHERE "received_at" IS NOT NULL ORDER BY datetime("received_at") DESC LIMIT 1; ================================================================================ TABLE: weather_description ================================================================================ name sql --------- -------------------------------------- name sql ------------------- ----------------------------------------- weather_description CREATE TABLE weather_description ( id INTEGER PRIMARY KEY AUTOINCREMENT, description_fi TEXT NOT NULL, description_en TEXT NOT NULL, wawa_code INTEGER, metar_code TEXT, nws_code TEXT ) PRAGMA table_info(weather_description); " cid name type notnull dflt_value pk --- -------------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 description_fi TEXT 1 0 2 description_en TEXT 1 0 3 wawa_code INTEGER 0 0 4 metar_code TEXT 0 0 5 nws_code TEXT 0 0 " INDEXES: FOREIGN KEYS: ================================================================================ TABLE: weather_fmi_artukainen_measurements ================================================================================ name sql --------- -------------------------------------- name sql ----------------------------------- -------------------------------------------------- weather_fmi_artukainen_measurements CREATE TABLE weather_fmi_artukainen_measurements ( measure_id INTEGER PRIMARY KEY, event_time TEXT NOT NULL UNIQUE, temperature REAL, wind_speed REAL, wind_gust REAL, wind_direction REAL, humidity REAL, dew_point REAL, precipitation REAL, precipitation_intensity REAL, snow_depth REAL, pressure REAL, visibility REAL, cloudiness REAL, weather REAL, source_raw_id INTEGER ) PRAGMA table_info(weather_fmi_artukainen_measurements); " cid name type notnull dflt_value pk --- ----------------------- ------- ------- ---------- -- 0 measure_id INTEGER 0 1 1 event_time TEXT 1 0 2 temperature REAL 0 0 3 wind_speed REAL 0 0 4 wind_gust REAL 0 0 5 wind_direction REAL 0 0 6 humidity REAL 0 0 7 dew_point REAL 0 0 8 precipitation REAL 0 0 9 precipitation_intensity REAL 0 0 10 snow_depth REAL 0 0 11 pressure REAL 0 0 12 visibility REAL 0 0 13 cloudiness REAL 0 0 14 weather REAL 0 0 15 source_raw_id INTEGER 0 0 " INDEXES: seq name unique origin partial --- ------------------------------------------------------ ------ ------ ------- 0 sqlite_autoindex_weather_fmi_artukainen_measurements_1 1 u 0 FOREIGN KEYS: ================================================================================ TABLE: weather_fmi_artukainen_raw ================================================================================ name sql --------- -------------------------------------- name sql -------------------------- ----------------------------------------- weather_fmi_artukainen_raw CREATE TABLE weather_fmi_artukainen_raw ( raw_id INTEGER PRIMARY KEY, received_at TEXT NOT NULL, raw_data TEXT NOT NULL, parsed INTEGER NOT NULL DEFAULT 0, parse_error TEXT ) PRAGMA table_info(weather_fmi_artukainen_raw); " cid name type notnull dflt_value pk --- ----------- ------- ------- ---------- -- 0 raw_id INTEGER 0 1 1 received_at TEXT 1 0 2 raw_data TEXT 1 0 3 parsed INTEGER 1 0 0 4 parse_error TEXT 0 0 " INDEXES: FOREIGN KEYS: -------------------------------------------------------------------------------- AIKAKENTTÄ: received_at -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18 07:05:01 Tunnistettu: TEXT Viimeisin alkuperäinen : 2026-08-18 07:05:01 SQLite/UTC : 2026-08-18 07:05:01 SUOMEN AIKA : 2026.08.18 10:05 VIIMEISIN AIKA SQL: SELECT "received_at" AS alkuperainen_timestamp, datetime("received_at") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("received_at"), 'localtime')) AS suomen_aika FROM "weather_fmi_artukainen_raw" WHERE "received_at" IS NOT NULL ORDER BY datetime("received_at") DESC LIMIT 1; ================================================================================ TABLE: weather_metar_clouds ================================================================================ name sql --------- -------------------------------------- name sql -------------------- ------------------------------------------------------------------- weather_metar_clouds CREATE TABLE weather_metar_clouds ( cloud_id INTEGER PRIMARY KEY, measure_id INTEGER NOT NULL, layer INTEGER NOT NULL, amount TEXT NOT NULL, base_m INTEGER, cloud_type TEXT, FOREIGN KEY (measure_id) REFERENCES weather_aviationweather_measurements(measure_id) ) PRAGMA table_info(weather_metar_clouds); " cid name type notnull dflt_value pk --- ---------- ------- ------- ---------- -- 0 cloud_id INTEGER 0 1 1 measure_id INTEGER 1 0 2 layer INTEGER 1 0 3 amount TEXT 1 0 4 base_m INTEGER 0 0 5 cloud_type TEXT 0 0 " INDEXES: seq name unique origin partial --- -------------------------------- ------ ------ ------- 0 idx_weather_metar_clouds_measure 0 c 0 FOREIGN KEYS: id seq table from to on_update on_delete match -- --- ------------------------------------ ---------- ---------- --------- --------- ----- 0 0 weather_aviationweather_measurements measure_id measure_id NO ACTION NO ACTION NONE ================================================================================ TABLE: weather_metar_stations ================================================================================ name sql --------- -------------------------------------- name sql ---------------------- ------------------------------------------ weather_metar_stations CREATE TABLE weather_metar_stations ( id INTEGER PRIMARY KEY, station TEXT NOT NULL UNIQUE, name TEXT, country TEXT, follow INTEGER NOT NULL DEFAULT 0 CHECK (follow IN (0,1)), start_date TEXT, end_date TEXT ) PRAGMA table_info(weather_metar_stations); " cid name type notnull dflt_value pk --- ---------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 station TEXT 1 0 2 name TEXT 0 0 3 country TEXT 0 0 4 follow INTEGER 1 0 0 5 start_date TEXT 0 0 6 end_date TEXT 0 0 " INDEXES: seq name unique origin partial --- ----------------------------------------- ------ ------ ------- 0 sqlite_autoindex_weather_metar_stations_1 1 u 0 FOREIGN KEYS: ================================================================================ TABLE: weather_metar_unknown ================================================================================ name sql --------- -------------------------------------- name sql --------------------- ------------------------------------ weather_metar_unknown CREATE TABLE weather_metar_unknown ( unknown_id INTEGER PRIMARY KEY, raw_id INTEGER NOT NULL, token TEXT NOT NULL ) PRAGMA table_info(weather_metar_unknown); " cid name type notnull dflt_value pk --- ---------- ------- ------- ---------- -- 0 unknown_id INTEGER 0 1 1 raw_id INTEGER 1 0 2 token TEXT 1 0 " INDEXES: seq name unique origin partial --- ----------------------------- ------ ------ ------- 0 idx_weather_metar_unknown_raw 0 c 0 FOREIGN KEYS: ================================================================================ TABLE: weather_utu_measurements ================================================================================ name sql --------- -------------------------------------- name sql ------------------------ ------------------------------------------------------------------------------------------------ weather_utu_measurements CREATE TABLE weather_utu_measurements ( measure_id INTEGER PRIMARY KEY, observed_at TEXT NOT NULL UNIQUE, received_at TEXT NOT NULL, temperature REAL, temperature_min_24h REAL, temperature_max_24h REAL, temperature_avg_24h REAL, dew_point REAL, wind_speed REAL, wind_direction REAL, rain_rate REAL, rain_24h REAL, humidity REAL, pressure REAL, pressure_trend_3h REAL , raw_id INTEGER) PRAGMA table_info(weather_utu_measurements); " cid name type notnull dflt_value pk --- ------------------- ------- ------- ---------- -- 0 measure_id INTEGER 0 1 1 observed_at TEXT 1 0 2 received_at TEXT 1 0 3 temperature REAL 0 0 4 temperature_min_24h REAL 0 0 5 temperature_max_24h REAL 0 0 6 temperature_avg_24h REAL 0 0 7 dew_point REAL 0 0 8 wind_speed REAL 0 0 9 wind_direction REAL 0 0 10 rain_rate REAL 0 0 11 rain_24h REAL 0 0 12 humidity REAL 0 0 13 pressure REAL 0 0 14 pressure_trend_3h REAL 0 0 15 raw_id INTEGER 0 0 " INDEXES: seq name unique origin partial --- ------------------------------------------- ------ ------ ------- 0 idx_weather_utu_measurements_raw_id 0 c 0 1 idx_utu_measurements_observed 0 c 0 2 sqlite_autoindex_weather_utu_measurements_1 1 u 0 FOREIGN KEYS: -------------------------------------------------------------------------------- AIKAKENTTÄ: received_at -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18 10:05:01 Tunnistettu: TEXT Viimeisin alkuperäinen : 2026-08-18 10:05:01 SQLite/UTC : 2026-08-18 10:05:01 SUOMEN AIKA : 2026.08.18 13:05 VIIMEISIN AIKA SQL: SELECT "received_at" AS alkuperainen_timestamp, datetime("received_at") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("received_at"), 'localtime')) AS suomen_aika FROM "weather_utu_measurements" WHERE "received_at" IS NOT NULL ORDER BY datetime("received_at") DESC LIMIT 1; ================================================================================ TABLE: weather_utu_raw ================================================================================ name sql --------- -------------------------------------- name sql --------------- ------------------------------------------------------------------------------- weather_utu_raw CREATE TABLE weather_utu_raw ( raw_id INTEGER PRIMARY KEY, received_at TEXT NOT NULL, raw_data TEXT NOT NULL, parse_status TEXT, parse_error TEXT , parser_status INTEGER NOT NULL DEFAULT 0) PRAGMA table_info(weather_utu_raw); " cid name type notnull dflt_value pk --- ------------- ------- ------- ---------- -- 0 raw_id INTEGER 0 1 1 received_at TEXT 1 0 2 raw_data TEXT 1 0 3 parse_status TEXT 0 0 4 parse_error TEXT 0 0 5 parser_status INTEGER 1 0 0 " INDEXES: seq name unique origin partial --- -------------------- ------ ------ ------- 0 idx_utu_raw_received 0 c 0 FOREIGN KEYS: -------------------------------------------------------------------------------- AIKAKENTTÄ: received_at -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18 10:05:01 Tunnistettu: TEXT Viimeisin alkuperäinen : 2026-08-18 10:05:01 SQLite/UTC : 2026-08-18 10:05:01 SUOMEN AIKA : 2026.08.18 13:05 VIIMEISIN AIKA SQL: SELECT "received_at" AS alkuperainen_timestamp, datetime("received_at") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("received_at"), 'localtime')) AS suomen_aika FROM "weather_utu_raw" WHERE "received_at" IS NOT NULL ORDER BY datetime("received_at") DESC LIMIT 1; ################################################################################ TIETOKANTA: zigbee.sqlite3 ################################################################################ TAULUT name ---------------- zb_data zb_device zb_health zb_health_device zb_linkquality zb_raw ================================================================================ TABLE: zb_data ================================================================================ name sql --------- -------------------------------------- name sql ------- -------------------------------- zb_data CREATE TABLE zb_data ( id INTEGER PRIMARY KEY, raw_id INTEGER NOT NULL, device_id INTEGER NOT NULL, ts TEXT NOT NULL, data_json TEXT NOT NULL, FOREIGN KEY (raw_id) REFERENCES zb_raw(id), FOREIGN KEY (device_id) REFERENCES zb_device(id) ) PRAGMA table_info(zb_data); " cid name type notnull dflt_value pk --- --------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 raw_id INTEGER 1 0 2 device_id INTEGER 1 0 3 ts TEXT 1 0 4 data_json TEXT 1 0 " INDEXES: seq name unique origin partial --- --------------------- ------ ------ ------- 0 idx_zb_data_device_ts 0 c 0 FOREIGN KEYS: id seq table from to on_update on_delete match -- --- --------- --------- -- --------- --------- ----- 0 0 zb_device device_id id NO ACTION NO ACTION NONE 1 0 zb_raw raw_id id NO ACTION NO ACTION NONE -------------------------------------------------------------------------------- AIKAKENTTÄ: ts -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18T07:06:56.331+00:00 Tunnistettu: ISO-8601 text Viimeisin alkuperäinen : 2026-08-18T07:06:56.331+00:00 SQLite/UTC : 2026-08-18 07:06:56 SUOMEN AIKA : 2026.08.18 10:06 VIIMEISIN AIKA SQL: SELECT "ts" AS alkuperainen_timestamp, datetime("ts") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("ts"), 'localtime')) AS suomen_aika FROM "zb_data" WHERE "ts" IS NOT NULL ORDER BY datetime("ts") DESC LIMIT 1; ================================================================================ TABLE: zb_device ================================================================================ name sql --------- -------------------------------------- name sql --------- -------------------------------------- zb_device CREATE TABLE zb_device ( id INTEGER PRIMARY KEY, ieee_address TEXT NOT NULL UNIQUE, friendly_name TEXT, manufacturer TEXT, model TEXT, zigbee_model TEXT, definition TEXT, device_type TEXT, network_role TEXT, power_source TEXT, room TEXT, protocol TEXT DEFAULT 'zigbee', firmware_id TEXT, firmware_version TEXT, seen_first TEXT NOT NULL, extra_json TEXT ) PRAGMA table_info(zb_device); " cid name type notnull dflt_value pk --- ---------------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 ieee_address TEXT 1 0 2 friendly_name TEXT 0 0 3 manufacturer TEXT 0 0 4 model TEXT 0 0 5 zigbee_model TEXT 0 0 6 definition TEXT 0 0 7 device_type TEXT 0 0 8 network_role TEXT 0 0 9 power_source TEXT 0 0 10 room TEXT 0 0 11 protocol TEXT 0 'zigbee' 0 12 firmware_id TEXT 0 0 13 firmware_version TEXT 0 0 14 seen_first TEXT 1 0 15 extra_json TEXT 0 0 " INDEXES: seq name unique origin partial --- ---------------------------- ------ ------ ------- 0 sqlite_autoindex_zb_device_1 1 u 0 FOREIGN KEYS: ================================================================================ TABLE: zb_health ================================================================================ name sql --------- -------------------------------------- name sql --------- -------------------------------- zb_health CREATE TABLE zb_health ( id INTEGER PRIMARY KEY, ts TEXT NOT NULL, response_time INTEGER, load_average_1 REAL, load_average_5 REAL, load_average_15 REAL, memory_used_mb REAL, memory_percent REAL, process_uptime_sec REAL, process_memory_used_mb REAL, process_memory_percent REAL, mqtt_connected INTEGER, mqtt_queued INTEGER, mqtt_published INTEGER, mqtt_received INTEGER, raw_json TEXT NOT NULL ) PRAGMA table_info(zb_health); " cid name type notnull dflt_value pk --- ---------------------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 ts TEXT 1 0 2 response_time INTEGER 0 0 3 load_average_1 REAL 0 0 4 load_average_5 REAL 0 0 5 load_average_15 REAL 0 0 6 memory_used_mb REAL 0 0 7 memory_percent REAL 0 0 8 process_uptime_sec REAL 0 0 9 process_memory_used_mb REAL 0 0 10 process_memory_percent REAL 0 0 11 mqtt_connected INTEGER 0 0 12 mqtt_queued INTEGER 0 0 13 mqtt_published INTEGER 0 0 14 mqtt_received INTEGER 0 0 15 raw_json TEXT 1 0 " INDEXES: seq name unique origin partial --- ---------------- ------ ------ ------- 0 idx_zb_health_ts 0 c 0 FOREIGN KEYS: -------------------------------------------------------------------------------- AIKAKENTTÄ: ts -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18T06:57:23.471+00:00 Tunnistettu: ISO-8601 text Viimeisin alkuperäinen : 2026-08-18T06:57:23.471+00:00 SQLite/UTC : 2026-08-18 06:57:23 SUOMEN AIKA : 2026.08.18 09:57 VIIMEISIN AIKA SQL: SELECT "ts" AS alkuperainen_timestamp, datetime("ts") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("ts"), 'localtime')) AS suomen_aika FROM "zb_health" WHERE "ts" IS NOT NULL ORDER BY datetime("ts") DESC LIMIT 1; -------------------------------------------------------------------------------- AIKAKENTTÄ: mqtt_received -------------------------------------------------------------------------------- SQL-tyyppi : INTEGER Esimerkki : 448 Tunnistettu: UNIX seconds Viimeisin alkuperäinen : 448 SQLite/UTC : 1970-01-01 00:07:28 SUOMEN AIKA : 1970.01.01 02:07 VIIMEISIN AIKA SQL: SELECT "mqtt_received" AS alkuperainen_timestamp, datetime("mqtt_received", 'unixepoch') AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("mqtt_received", 'unixepoch'), 'localtime')) AS suomen_aika FROM "zb_health" WHERE "mqtt_received" IS NOT NULL ORDER BY datetime("mqtt_received", 'unixepoch') DESC LIMIT 1; ================================================================================ TABLE: zb_health_device ================================================================================ name sql --------- -------------------------------------- name sql ---------------- ------------------------------------ zb_health_device CREATE TABLE zb_health_device ( id INTEGER PRIMARY KEY, health_id INTEGER NOT NULL, device_id INTEGER, ts TEXT NOT NULL, messages INTEGER, messages_per_sec REAL, leave_count INTEGER, network_address_changes INTEGER, FOREIGN KEY (health_id) REFERENCES zb_health(id), FOREIGN KEY (device_id) REFERENCES zb_device(id) ) PRAGMA table_info(zb_health_device); " cid name type notnull dflt_value pk --- ----------------------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 health_id INTEGER 1 0 2 device_id INTEGER 0 0 3 ts TEXT 1 0 4 messages INTEGER 0 0 5 messages_per_sec REAL 0 0 6 leave_count INTEGER 0 0 7 network_address_changes INTEGER 0 0 " INDEXES: seq name unique origin partial --- ----------------------- ------ ------ ------- 0 idx_zb_health_device_ts 0 c 0 FOREIGN KEYS: id seq table from to on_update on_delete match -- --- --------- --------- -- --------- --------- ----- 0 0 zb_device device_id id NO ACTION NO ACTION NONE 1 0 zb_health health_id id NO ACTION NO ACTION NONE -------------------------------------------------------------------------------- AIKAKENTTÄ: ts -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18T06:57:23.471+00:00 Tunnistettu: ISO-8601 text Viimeisin alkuperäinen : 2026-08-18T06:57:23.471+00:00 SQLite/UTC : 2026-08-18 06:57:23 SUOMEN AIKA : 2026.08.18 09:57 VIIMEISIN AIKA SQL: SELECT "ts" AS alkuperainen_timestamp, datetime("ts") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("ts"), 'localtime')) AS suomen_aika FROM "zb_health_device" WHERE "ts" IS NOT NULL ORDER BY datetime("ts") DESC LIMIT 1; ================================================================================ TABLE: zb_linkquality ================================================================================ name sql --------- -------------------------------------- name sql -------------- -------------------------------- zb_linkquality CREATE TABLE zb_linkquality ( id INTEGER PRIMARY KEY, raw_id INTEGER NOT NULL, device_id INTEGER NOT NULL, ts TEXT NOT NULL, linkquality INTEGER, FOREIGN KEY (raw_id) REFERENCES zb_raw(id), FOREIGN KEY (device_id) REFERENCES zb_device(id) ) PRAGMA table_info(zb_linkquality); " cid name type notnull dflt_value pk --- ----------- ------- ------- ---------- -- 0 id INTEGER 0 1 1 raw_id INTEGER 1 0 2 device_id INTEGER 1 0 3 ts TEXT 1 0 4 linkquality INTEGER 0 0 " INDEXES: seq name unique origin partial --- ---------------------------- ------ ------ ------- 0 idx_zb_linkquality_device_ts 0 c 0 FOREIGN KEYS: id seq table from to on_update on_delete match -- --- --------- --------- -- --------- --------- ----- 0 0 zb_device device_id id NO ACTION NO ACTION NONE 1 0 zb_raw raw_id id NO ACTION NO ACTION NONE -------------------------------------------------------------------------------- AIKAKENTTÄ: ts -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18T07:06:56.921+00:00 Tunnistettu: ISO-8601 text Viimeisin alkuperäinen : 2026-08-18T07:06:56.331+00:00 SQLite/UTC : 2026-08-18 07:06:56 SUOMEN AIKA : 2026.08.18 10:06 VIIMEISIN AIKA SQL: SELECT "ts" AS alkuperainen_timestamp, datetime("ts") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("ts"), 'localtime')) AS suomen_aika FROM "zb_linkquality" WHERE "ts" IS NOT NULL ORDER BY datetime("ts") DESC LIMIT 1; ================================================================================ TABLE: zb_raw ================================================================================ name sql --------- -------------------------------------- name sql ------ -------------------------------- zb_raw CREATE TABLE zb_raw ( id INTEGER PRIMARY KEY, ts TEXT NOT NULL, device_id INTEGER, topic TEXT NOT NULL, payload_json TEXT NOT NULL, FOREIGN KEY (device_id) REFERENCES zb_device(id) ) PRAGMA table_info(zb_raw); " cid name type notnull dflt_value pk --- ------------ ------- ------- ---------- -- 0 id INTEGER 0 1 1 ts TEXT 1 0 2 device_id INTEGER 0 0 3 topic TEXT 1 0 4 payload_json TEXT 1 0 " INDEXES: seq name unique origin partial --- -------------------- ------ ------ ------- 0 idx_zb_raw_ts 0 c 0 1 idx_zb_raw_device_ts 0 c 0 FOREIGN KEYS: id seq table from to on_update on_delete match -- --- --------- --------- -- --------- --------- ----- 0 0 zb_device device_id id NO ACTION NO ACTION NONE -------------------------------------------------------------------------------- AIKAKENTTÄ: ts -------------------------------------------------------------------------------- SQL-tyyppi : TEXT Esimerkki : 2026-08-18T07:06:56.921+00:00 Tunnistettu: ISO-8601 text Viimeisin alkuperäinen : 2026-08-18T07:06:56.331+00:00 SQLite/UTC : 2026-08-18 07:06:56 SUOMEN AIKA : 2026.08.18 10:06 VIIMEISIN AIKA SQL: SELECT "ts" AS alkuperainen_timestamp, datetime("ts") AS muunnettu_utc, strftime('%Y.%m.%d %H:%M', datetime(datetime("ts"), 'localtime')) AS suomen_aika FROM "zb_raw" WHERE "ts" IS NOT NULL ORDER BY datetime("ts") DESC LIMIT 1; ================================================================================ RAPORTTI LOPPU ================================================================================