Notes.txt

#
#    Notes.txt -- Notes about this project that are useful to have around.
#    Copyright (C) 2015  Thomas A. DeMay
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 3 of the License, or
#    any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License along
#    with this program; if not, write to the Free Software Foundation, Inc.,
#    51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
#

/*! Custom Process Steps: */
+----------+------------------------------------------------------------+--------------+
| Command  | Arguments                                                  | Working Dir  |
+==========+============================================================+==============+
| bash     | GetArchiveTag.sh                                           | %{sourceDir} |
+----------+------------------------------------------------------------+--------------+
| bash     | %{sourceDir}/DoLink.sh %{CurrentProject:Name} %{sourceDir} | %{buildDir}  |
+----------+------------------------------------------------------------+--------------+
| doxygen  |  >DoxygenLog.txt                                           | %{sourceDir} |
+----------+------------------------------------------------------------+--------------+

GetArchiveTag.sh is a shell script to get the Git archive tag and put it into ArchiveTag.txt
DoLink.sh is a shell script to put a link to the executable in the source directory.
The doxygen step puts the stdout data into a file for later perusal.  Doxyfile is in source dir.


Meter id numbers:
    House electrical and water:  300002570

# Shell script to create SQL to set the timezone to local STANDARD time for the location
# of the machine running the script.

# get the timezone offset for jan 1 at midnight -- will always be local standard time.
standardTimeZoneOffset=$(date -j 01010000 +%z)
# get the length not including the last two characters
szend=$(( ${#standardTimeZoneOffset} - 2 ))
# Break the time zone offset string into hour and minute segments
stzHour=${standardTimeZoneOffset:0:$szend}
stzMin=${standardTimeZoneOffset:$szend}
# if the hour part has neither a "-" nor "+" at the begin, prepend "+".
if [ "${stzHour#-}" = "${stzHour}" ] && [ "${stzHour#+}" = "${stzHour}" ]; then stzHour="+${stzHour}"; fi
# glue the two parts of tz offset together to form SQL statement to set time zone.
setTimeZone="SET time_zone='${stzHour}:${stzMin}';"


Information about changing to and from Daylight Savings Time in the UnitedStates/Pacific timezone.

Year	Date & Time             Abbreviation	Time Change             Offset After
2014	Sun, Mar 9, 2:00 AM	PST → PDT	+1 hour (DST start)	UTC-7h
        Sun, Nov 2, 2:00 AM	PDT → PST	-1 hour (DST end)	UTC-8h
2015	Sun, Mar 8, 2:00 AM	PST → PDT	+1 hour (DST start)	UTC-7h
        Sun, Nov 1, 2:00 AM	PDT → PST	-1 hour (DST end)	UTC-8h
2016	Sun, Mar 13, 2:00 AM	PST → PDT	+1 hour (DST start)	UTC-7h
        Sun, Nov 6, 2:00 AM	PDT → PST	-1 hour (DST end)	UTC-8h
2017	Sun, Mar 12, 2:00 AM	PST → PDT	+1 hour (DST start)	UTC-7h
        Sun, Nov 5, 2:00 AM	PDT → PST	-1 hour (DST end)	UTC-8h
2018	Sun, Mar 11, 2:00 AM	PST → PDT	+1 hour (DST start)	UTC-7h
        Sun, Nov 4, 2:00 AM	PDT → PST	-1 hour (DST end)	UTC-8h
* All times are local Los Angeles time.


SQL select statement to extract interesting things from the saved meter data.

SELECT ComputerTime
    , MeterTime
    , ROUND(MID(MeterData, 17, 8)*0.01, 3) AS HouseKwh
    , ROUND((MID(MeterData, 204, 8) +  MID(MeterData, 212, 8)) * .001, 3) AS WaterSysKwh
    , ROUND(MID(MeterData, 220, 8) * 0.1, 1) AS CuFtWater
    FROM RawMeterData
    WHERE DataType = 'V4A';

This view extracts parameters that I'm interested in for my configuration of meters.
There is going to be an incredible glitch when a count overflows.
Note that the meter serial number and data type are embedded in the table name.

CREATE OR REPLACE VIEW MeterData AS
SELECT rmd.ComputerTime AS Time
, ROUND(MID(rmd.MeterData, 220, 8) * 0.1, 1) AS CuFtWater

, ROUND((MID(rmd.MeterData, 220, 8)
- MID(rmd1.MeterData, 220, 8)) / ((UNIX_TIMESTAMP(rmd.ComputerTime) + MICROSECOND(rmd.ComputerTime)/1000000.0)
- (UNIX_TIMESTAMP(rmd1.ComputerTime) + MICROSECOND(rmd1.ComputerTime)/1000000.0))  * 0.74805194703778 * 60, 3) AS GPM

, ROUND(MID(rmd.MeterData, 17, 8) / POWER(10, MID(rmd.MeterData, 231, 1)), 2) AS HouseEnergyKWH

, MID(rmd.MeterData, 153, 7) + 0.0 AS HousePowerW

, ROUND(
 ((MID(rmd.MeterData, 17, 8) - MID(rmd1.MeterData, 17, 8)) * 1000.0 / POWER(10, MID(rmd.MeterData, 231, 1)))  /* Energy in period in wattHours */
/  /*---------------------------------------------------divided by---------------------------------*/
(((UNIX_TIMESTAMP(rmd.ComputerTime) + MICROSECOND(rmd.ComputerTime)/1000000.0)         /* Time period duration */
- (UNIX_TIMESTAMP(rmd1.ComputerTime) + MICROSECOND(rmd1.ComputerTime)/1000000.0)) / 3600.0)      /* in Hours */
 , 0) AS AvgPowerW                   /* rounded to zero decimal places, in watts */

, ROUND((MID(rmd.MeterData, 204, 8) +  MID(rmd.MeterData, 212, 8)) * .001, 3) AS WaterSysKwh
, ROUND(((MID(rmd.MeterData, 204, 8) +  MID(rmd.MeterData, 212, 8)) - (MID(rmd1.MeterData, 204, 8) +  MID(rmd1.MeterData, 212, 8)) )  /* Energy in period in wattHours */
/ /*---------------------------------------------------divided by---------------------------------*/
(((UNIX_TIMESTAMP(rmd.ComputerTime) + MICROSECOND(rmd.ComputerTime)/1000000.0)         /* Time period duration */
- (UNIX_TIMESTAMP(rmd1.ComputerTime) + MICROSECOND(rmd1.ComputerTime)/1000000.0)) / 3600.0)      /* in Hours */
    , 0) AS AvgWaterPowerW                   /* Average power used by water system in watts. */

  FROM 000300002570_A_RawMeterData as rmd
     INNER JOIN 000300002570_A_RawMeterData AS rmd1 ON
         rmd1.idRawMeterData = rmd.idRawMeterData -1        /* Join this record with previous as defined by id number. */
;

SQL to extract records from the DebugInfo table starting from 2310 yesterday.
SELECT Time
, RIGHT(ArchiveTag,8) as TAG    /* Only show 8 characters of the ArchiveTag */
, Severity
, SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(FunctionName, '::', -1), '(', 1), ' ', -1) as Function /* Display just the function name. */
, SourceLineNo as Line
, LEFT(REPLACE(REPLACE(Message,'\r','\\r'),'\n','\\n'), 240)  /* Only show first 240 chars of message, replacing \r and \n with "\r" and "\n" */
 FROM DebugInfo WHERE Time > TIMESTAMPADD(MINUTE, -50, DATE(NOW()))      /* 50 minutes before midnight today. */
 /* AND ArchiveTag LIKE 'notset' */ /* To see only program starts. */
;


Example time set sequence from EKM Dash program:
Send Request v4A
/  ?  0  0  0  3  0  0  0  0  2  5  7  0  0  0  ! \r \n
2F 3F 30 30 30 33 30 30 30 30 32 35 37 30 30 30
21 0D 0A

Receive   response v4A
02 10 24 19 30 30 30 33 30 30 30 30 32 35 37 30
30 30 30 30 34 35 34 31 30 30 30 30 32 30 32 33
30 30 30 30 30 30 30 30 30 30 30 30 31 33 33 32
30 30 30 30 33 32 30 38 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 34 35 34 31
30 30 30 30 30 30 30 30 31 32 31 31 31 32 31 34
30 30 30 30 30 30 30 36 30 30 30 30 34 38 30 30
30 30 30 30 30 30 30 36 30 30 30 30 30 30 35 32
38 30 30 30 30 30 30 30 30 30 30 31 31 33 32 43
30 39 39 43 30 39 37 43 30 30 30 30 30 30 30 30
38 30 30 30 30 30 31 34 30 30 30 30 30 30 30 30
30 30 30 30 32 32 30 36 30 30 35 30 30 30 31 36
34 32 32 30 30 30 31 36 33 35 38 30 30 30 30 36
33 38 33 30 31 33 32 30 30 31 35 30 39 30 31 30
33 31 30 30 36 32 32 30 30 21 0D 0A 03 09 13

Send  password
SOH P 1 STX (  0  0  0  0  0  0  0  0  ) ETX CRC1 CRC2
01 50 31 02 28 30 30 30 30 30 30 30 30 29 03 32
44

Receive
ACK
06

2015-09-01 day 3 (Tue) 10:06:20
Send Time w/ CRC
SOH W 1 STX 0  0  6  0  (  1  5  0  9  0  1  0  3  1  0  0  6  2  0  ) ETX CRC1 CRC2
01 57 31 02 30 30 36 30 28 31 35 30 39 30 31 30 33 31 30 30 36 32 30 29 03 55 62

Receive
ACK
06

Send end message
SOH B 0 ETX u
01 42 30 03 75

/***************************************************************/
At idRawMeterData = 2784 I finally got the meter set to local standard time.
idRawMeterData = 2783 is last record with MeterTime at local time (PDT).
Records AFTER 2783 have date-time in the MeterData matching the time in the MeterTime column.
Records BEFORE AND INCLUDING 2783 have date-time in MeterData one hour later than MeterTime column.

/***************************************************************/
This SQL sums the north and south solar arrays for today only.
{From a table that collects data from solar inverters every 15 min.}
SELECT se.Time
,  ROUND((se.LifeWattHour + se1.LifeWattHour) / 1000.0, 3) AS 'Solar KWH'
, (se.OutWattsNow + se1.OutWattsNow) AS OutWatts
    FROM SolarEnergy as se INNER JOIN
        SolarEnergy as se1 ON se.Name LIKE 'south%' AND ABS(UNIX_TIMESTAMP(se1.Time) - UNIX_TIMESTAMP(se.Time)) < 450 /* within 7.5 min */
    WHERE se.Name LIKE 'north%' AND se.Time > DATE(NOW());