research why [dbui] day_report_dates have offsets

[JCKalt]

Need to understand why when I replaced the query in [dbui] that pulls directly from device_data table with the query in dev-hist-for-device-sum-by-day.sql that pulls from device_hist view (as follows):

\prompt 'Enter device serial (or press enter for B140016B): ' device_serial \set device_serial_choosen '\'B140016B\''

SELECT COALESCE(:'device_serial', 'B140016B') AS device_serial_result \gset device_serial_choosen

SELECT day_report_date, now() as now, ROUND((SUM(wh1)/1000)::numeric, 3) AS kwh1, ROUND((SUM(wh2)/1000)::numeric, 3) AS kwh2, ROUND((SUM(wh3)/1000)::numeric, 3) AS kwh3, ROUND((SUM(total_wh)/1000)::numeric, 3) AS total_kwh FROM device_hist h WHERE device_serial = :device_serial_choosen GROUP BY day_report_date HAVING SUM(total_wh) > 0 ORDER BY day_report_date;

The day_report_dates had a time portion other than 00. It seems to be different than psql on my mac, but not sure if numerical results are different. Need to pin this down to avoid errors using these views especially when columns made of computed expressions are used in group by statements.

It's not clear how to pin this down so that environments are set up correctly with respect to local time zones, etc. It would be best if they were all set to UTC in psql and in clients that pull from databases.

For example we cannot have views acting differently when timezones are changed, especially if dev and production environments will vary.

[JCKalt]

mac psql environment should be UTC. Make sure this does not break current view. (I.e. if view works regardless of local timezone-- is this something that might break down the road. It would be better if it worked with UTC and make sure all sql clients local tz is UTC.

[JCKalt]

Good news is that this script: sql/dev-hist-for-display-group-sum-by-day.sql For Display Group 23 returns this row: day_report_date | kwh1 | kwh2 | kwh3 | total_kwh 2022-10-18 | 69.852 | 68.452 | 69.576 | 207.880

And 207.880 matches Eyedro UI value when running Compare for that date from 5pm to 5pm.

the script groups total_kwh by day_report_date as follows:

SELECT day_report_date,
       ROUND((SUM(wh1)/1000)::numeric, 3) AS kwh1,
       ROUND((SUM(wh2)/1000)::numeric, 3) AS kwh2,
       ROUND((SUM(wh3)/1000)::numeric, 3) AS kwh3,
       ROUND((SUM(total_wh)/1000)::numeric, 3) AS total_kwh
 FROM device_hist h
INNER JOIN device_display_group_map map USING (device_id)
WHERE display_group_id = :display_group_id_choosen  (set to 23)
GROUP BY day_report_date
HAVING SUM(total_wh) > 0
ORDER BY day_report_date;

device_hist.day_report_date is defined as:

    SELECT ...,
    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
            ) AT TIME ZONE 'UTC',  'YYYY-MM-DD'
        ), 'YYYY-MM-DD'
    ) AS day_report_date
 FROM device_data dd
INNER JOIN device dev USING(device_serial);

[JCKalt]

It seems like removing the to_date wrapper would guarantee that the expression would be independent of any local timezone influences. It would be good to create a view that does this and compare to see of the output looks the same.

[JCKalt]

I created new view that looks like this:

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,

and data looks the same for sql/dev-hist-for-display-group-sum-by-day.sql

[JCKalt]

It is probably the case that a date to to human translation is done in the local timezone. By changing the view to report to_char date values it guarantees to be timezone-less.