check eydro UI against API results using curl script get-DeviceData.sh (gg db)../

Checking DB results against Eyedro UI for device_data. created special display group for a single device_serial value 'Just Device B140016B'

checking against values fetched from database for kwh on 18-oct-22 5pm - 19-oct-22 5pm using the

timezone that eyedro API reports for B140016B which is "Pacific/Pitcairn" epoch-to-human 1666141200 "Pacific/Pitcairn" 2022-10-18 17:00:00

getting small rounding errors between (gg db)/sql/eyedro= psql -> # \i sql/dev-hist-for-device-sum-by-day.sql which returns: day_report_date | kwh1 | kwh2 | kwh3 | total_kwh -----------------+--------+--------+--------+----------- 2022-10-18 | 18.432 | 17.392 | 18.359 | 54.183

while eyedro UI when changing display group for ports a,b & c returns following totals: Port A: 18.432 kWh Port B: 17.390 kWh Port C: 18.381 kWh Ports A,B &C: 54.203 kWh

Spreadsheet analysis of curl fetch using ../get-DeviceData.sh

gg db

../get-DeviceData.sh \
  --method "DeviceData" \
  --device-serial "B140016B" \
  --date-start-sec-utc "1666141200" \
  --steps "24" \
  --user-key "xxxxxxxxxxxx" \
  -f -ro | jq | src/flatten-DeviceData.py > flatten.csv

18.432 | 17.39 | 18.381 | 54.203. Matching Eyedro UI so database query needs rounding fixes.

It's clear that device_hist view is incorrectly computing the day_report_date incorrectly.

to_date( to_char( to_timestamp(epoch_date_stamp + CAST(epoch_date_stamp - EXTRACT(epoch FROM (to_timestamp(epoch_date_stamp) AT TIME ZONE 'UTC' AT TIME ZONE tz)) AS INTEGER) - dd.day_start_seconds), 'YYYY-MM-DD' ), 'YYYY-MM-DD' ) AS day_report_date,

  --method "DeviceData" \
  --device-serial "B140016B" \
  --date-start-sec-utc "1666141200" \
  --steps "24" \
  --user-key "Pla8keiQ7i4PG4gzKMgGZ2c8dTs2BQAmq9sA4FvZ" \
  --force -ro | src/flatten-DeviceData.py

Gives:

timestamp,timezone,epoch_timestamp,W1,W2,W3,A1,A2,A3,V1,V2,V3,Kwh1,Kwh2,Kwh3
2022-10-18 17:00:00,Pacific/Pitcairn,1666141200,719.723,690.03,730.805,5.469,5.242,5.551,132.874,132.97,132.934,0.719,0.69,0.73
2022-10-18 18:00:00,Pacific/Pitcairn,1666144800,729.485,684.535,742.992,5.517,5.173,5.617,133.384,133.478,133.449,0.729,0.684,0.743
2022-10-18 19:00:00,Pacific/Pitcairn,1666148400,715.077,678.561,738.174,5.438,5.157,5.61,132.7,132.796,132.769,0.714,0.679,0.737
2022-10-18 20:00:00,Pacific/Pitcairn,1666152000,729.578,687.313,745.782,5.514,5.19,5.634,133.534,133.629,133.601,0.729,0.688,0.745
2022-10-18 21:00:00,Pacific/Pitcairn,1666155600,734.802,689.841,745.093,5.541,5.201,5.62,133.761,133.857,133.832,0.735,0.69,0.745
2022-10-18 22:00:00,Pacific/Pitcairn,1666159200,733.604,686.446,741.253,5.542,5.187,5.599,133.557,133.651,133.629,0.734,0.686,0.742
2022-10-18 23:00:00,Pacific/Pitcairn,1666162800,738.627,694.956,745.405,5.562,5.231,5.613,134.052,134.149,134.124,0.738,0.695,0.745
2022-10-19 00:00:00,Pacific/Pitcairn,1666166400,736.329,692.232,735.798,5.56,5.227,5.553,133.713,133.809,133.781,0.737,0.692,0.736
2022-10-19 01:00:00,Pacific/Pitcairn,1666170000,741.564,697.058,739.127,5.586,5.25,5.565,134.048,134.141,134.121,0.742,0.697,0.739
2022-10-19 02:00:00,Pacific/Pitcairn,1666173600,744.242,700.47,742.493,5.598,5.267,5.579,134.286,134.375,134.359,0.744,0.7,0.743
2022-10-19 03:00:00,Pacific/Pitcairn,1666177200,740.876,696.174,737.646,5.586,5.25,5.559,133.952,134.04,134.024,0.74,0.696,0.737
2022-10-19 04:00:00,Pacific/Pitcairn,1666180800,734.713,691.313,736.011,5.556,5.227,5.563,133.604,133.692,133.68,0.734,0.692,0.736
2022-10-19 05:00:00,Pacific/Pitcairn,1666184400,732.362,691.249,736.817,5.539,5.227,5.569,133.565,133.654,133.637,0.733,0.691,0.736
2022-10-19 06:00:00,Pacific/Pitcairn,1666188000,735.211,693.334,734.311,5.559,5.243,5.549,133.595,133.677,133.661,0.735,0.694,0.735
2022-10-19 07:00:00,Pacific/Pitcairn,1666191600,731.064,689.508,732.02,5.544,5.228,5.549,133.132,133.222,133.187,0.731,0.689,0.733
2022-10-19 08:00:00,Pacific/Pitcairn,1666195200,721.18,691.241,721.384,5.501,5.271,5.501,132.487,132.573,132.533,0.721,0.691,0.721
2022-10-19 09:00:00,Pacific/Pitcairn,1666198800,1005.128,933.52,950.894,7.888,7.308,7.444,132.221,132.311,132.268,1.005,0.934,0.95
2022-10-19 10:00:00,Pacific/Pitcairn,1666202400,711.029,688.543,715.986,5.446,5.269,5.482,132.03,132.119,132.078,0.711,0.689,0.717
2022-10-19 11:00:00,Pacific/Pitcairn,1666206000,705.048,688.88,714.787,5.411,5.281,5.481,131.844,131.935,131.894,0.705,0.689,0.714
2022-10-19 12:00:00,Pacific/Pitcairn,1666209600,709.61,684.154,717.649,5.439,5.241,5.498,131.861,131.953,131.91,0.709,0.683,0.718
2022-10-19 13:00:00,Pacific/Pitcairn,1666213200,709.372,679.042,722.862,5.434,5.202,5.535,131.819,131.91,131.867,0.708,0.679,0.723
2022-10-19 14:00:00,Pacific/Pitcairn,1666216800,705.184,679.616,709.431,5.423,5.223,5.455,131.438,131.525,131.481,0.705,0.68,0.709
2022-10-19 15:00:00,Pacific/Pitcairn,1666220400,1000.482,922.132,954.93,7.912,7.274,7.529,131.715,131.803,131.764,1.0,0.922,0.955
2022-10-19 16:00:00,Pacific/Pitcairn,1666224000,1174.645,1060.323,1092.035,9.312,8.388,8.632,132.262,132.352,132.318,1.174,1.06,1.092
totals,,,18438.935,17390.471,18383.685,140.877,132.757,140.287,3191.434,3193.621,3192.901,18.432,17.39,18.381

then

echo "18.432+17.39+18.381" | bc 

54.203

This matchines Eyedro UI for that device and date starting at 5pm local time of device (pacific whatever) so he mission now is to fix the database view for device_hist so that it correctly computes the day start timestamp and then the totals should be the same.

Wait--- not so fast.

The important idea here is that a) we will be writing "tags" into each device_data row that we wish to be found in a reporting partition we then wish to have the correct computation in the device_hist view so that the rows are correctly identified with the "reporting date" so that all 24 of them are correctly included as they are above when the specific date is queries 2022-10-18 in this case. We are introducing into the data the idea of a "reporting" of the data. It's a sort of meta data operation that will allow us to quickly assemble a given report from it's data.

We are taking a short cut by writing these data elements into the device_data table iteself. A more formal reporting object may be more desirable in the future, but that introduces more complexity. If it is true that there will always be a primary "report" representing the data, why not tag the data itself-- this hopefully will reduce errors and complexity.

Got day_report_timestamp column below to work:

-- Create or replace the device_hist view. -- This view presents the device data along with additional computed columns: -- - offset_seconds: The time difference in seconds between the device's timezone and UTC. -- A negative value indicates that the local time is behind UTC. -- - local_time: A human-readable representation of the device's local time. -- - day_report_date: A date representation that takes into account the device's local time and day_start_seconds. -- It calculates the report's local date by adding the timezone offset to epoch_date_stamp and then -- subtracting the day_start_seconds. This effectively shifts the start of the "day" as per the device's settings. DROP VIEW IF EXISTS device_hist; CREATE OR REPLACE VIEW device_hist AS SELECT dev.device_id, dd.device_serial, dd.epoch_date_stamp, dd.date_stamp, dd.site_id, dd.tz, dd.day_start_seconds, dd.energy_saver_on, dd.w1_value, dd.w2_value, dd.w3_value, dd.a1_value, dd.a2_value, dd.a3_value, dd.v1_value, dd.v2_value, dd.v3_value, dd.wh1_value, dd.wh2_value, dd.wh3_value,

-- Computed column: Time zone offset in seconds. Negative for timezones behind UTC.
CAST(epoch_date_stamp - EXTRACT(epoch FROM (to_timestamp(epoch_date_stamp) AT TIME ZONE 'UTC' AT TIME ZONE tz)) AS INTEGER) AS offset_seconds,

-- Computed column: Local time in 'YYYY-MM-DD HH24:MI:SS' format.
to_char(
    to_timestamp(epoch_date_stamp + CAST(epoch_date_stamp - EXTRACT(epoch FROM (to_timestamp(epoch_date_stamp) AT TIME ZONE 'UTC' AT TIME ZONE tz)) AS INTEGER)),
    'YYYY-MM-DD HH24:MI:SS'
) AS local_time,

-- Computed column: The local date for reporting, considering day_start_seconds and time zone offset.
to_date(
    to_char(
        to_timestamp(epoch_date_stamp + CAST(epoch_date_stamp - EXTRACT(epoch FROM (to_timestamp(epoch_date_stamp) AT TIME ZONE 'UTC' AT TIME ZONE tz)) AS INTEGER) - dd.day_start_seconds),
        'YYYY-MM-DD'
    ),
    'YYYY-MM-DD'
) AS day_report_date,

to_timestamp(
    epoch_date_stamp +
    CAST(epoch_date_stamp -
        EXTRACT(epoch FROM (to_timestamp(epoch_date_stamp) AT TIME ZONE 'UTC' AT TIME ZONE tz)) AS INTEGER) -
        dd.day_start_seconds
) AT TIME ZONE 'UTC' AS day_report_timestamp,

COALESCE(dd.wh1_value, 0) / 1000 AS kwh1,
COALESCE(dd.wh2_value, 0) / 1000 AS kwh2,
COALESCE(dd.wh3_value, 0) / 1000 AS kwh3,
(COALESCE(dd.wh1_value, 0) + COALESCE(dd.wh2_value, 0) + COALESCE(dd.wh3_value, 0)) / 1000 AS total_kwh

FROM device_data dd INNER JOIN device dev USING(device_serial);

Get this result:

select day_report_date, day_report_timestamp from device_hist where device_serial = 'B140016B' and epoch_date_stamp >= 1666141200 and epoch_date_stamp < 1666141200 + (3600*24) order by epoch_date_stamp;

day_report_date | day_report_timestamp -----------------+---------------------- 2022-10-17 | 2022-10-18 00:00:00 2022-10-17 | 2022-10-18 01:00:00 2022-10-17 | 2022-10-18 02:00:00 2022-10-17 | 2022-10-18 03:00:00 2022-10-18 | 2022-10-18 04:00:00 2022-10-18 | 2022-10-18 05:00:00 2022-10-18 | 2022-10-18 06:00:00 2022-10-18 | 2022-10-18 07:00:00 2022-10-18 | 2022-10-18 08:00:00 2022-10-18 | 2022-10-18 09:00:00 2022-10-18 | 2022-10-18 10:00:00 2022-10-18 | 2022-10-18 11:00:00 2022-10-18 | 2022-10-18 12:00:00 2022-10-18 | 2022-10-18 13:00:00 2022-10-18 | 2022-10-18 14:00:00 2022-10-18 | 2022-10-18 15:00:00 2022-10-18 | 2022-10-18 16:00:00 2022-10-18 | 2022-10-18 17:00:00 2022-10-18 | 2022-10-18 18:00:00 2022-10-18 | 2022-10-18 19:00:00 2022-10-18 | 2022-10-18 20:00:00 2022-10-18 | 2022-10-18 21:00:00 2022-10-18 | 2022-10-18 22:00:00 2022-10-18 | 2022-10-18 23:00:00 (24 rows)

JCKalt / General-Work

check eydro UI against API results using curl script get-DeviceData.sh (gg db)../ #115