TED Pro MTU Second Data

[SpaceGuy]

I am a new owner of a TED Pro Home with a 4-60A, 4-20A Spyder. Though we had a pretty good suspicion (it is nearly 30 years old), TED confirmed our hot water heater is eating electricity.

After getting the MTU and Spyder installed, the next task is setting up a database to store the data. The ECC is polled via the http://..../history.export.raw, then error checking is performed, i.e. checksum matches. Finally, the data is inserted into the database. The database has a unique constraint on the measurement type (second, minute, hour, ...), device (MTU, Spyder circuit 1, 2, 3, etc), measurement time. When querying the ECC for the MTU second data, my code is throwing a lot of errors about duplicate data. While reviewing the data, it appears the timestamp is being reported multiple times. See the messages below, the interesting fields are 'timestamp' and 'source'

This is the URL: http://.../history/export.raw?S=1478484245&M=1&D=0&T=1

Is this expected behavior?

If so, how should data from the same time be handled, first is best, drop the rest or sum all the values, or last is best?

If not, any ideas on what may be causing duplicate data?

Thanks!

Jack

Code: [Select]

2016-11-06 21:15:51,236 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484252, 'active_power': 1387, 'zerodata': False, 'source': '{164, 28, 225, 31, 88, 107, 5, 0, 0, 13, 0, 0, 0, 183, 4, 80}', 'cost': 13, 'voltage': 1207, 'circuit': 1, 'chksum': 80}
2016-11-06 21:15:51,248 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484261, 'active_power': 1387, 'zerodata': False, 'source': '{164, 37, 225, 31, 88, 107, 5, 0, 0, 13, 0, 0, 0, 183, 4, 89}', 'cost': 13, 'voltage': 1207, 'circuit': 1, 'chksum': 89}
2016-11-06 21:15:51,256 - query_ted - INFO - {"message": "duplicate key value violates unique constraint \"measurement_device_id_measurement_type_id_measurement_time_key\""}
2016-11-06 21:15:51,257 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484252, 'active_power': 1387, 'zerodata': False, 'source': '{164, 28, 225, 31, 88, 107, 5, 0, 0, 13, 0, 0, 0, 183, 4, 80}', 'cost': 13, 'voltage': 1207, 'circuit': 1, 'chksum': 80}
2016-11-06 21:15:51,261 - query_ted - INFO - {"message": "duplicate key value violates unique constraint \"measurement_device_id_measurement_type_id_measurement_time_key\""}
2016-11-06 21:15:51,262 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484251, 'active_power': 1379, 'zerodata': False, 'source': '{164, 27, 225, 31, 88, 99, 5, 0, 0, 13, 0, 0, 0, 181, 4, 69}', 'cost': 13, 'voltage': 1205, 'circuit': 1, 'chksum': 69}
2016-11-06 21:15:51,269 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484250, 'active_power': 1374, 'zerodata': False, 'source': '{164, 26, 225, 31, 88, 94, 5, 0, 0, 13, 0, 0, 0, 182, 4, 64}', 'cost': 13, 'voltage': 1206, 'circuit': 1, 'chksum': 64}
2016-11-06 21:15:51,278 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484249, 'active_power': 1377, 'zerodata': False, 'source': '{164, 25, 225, 31, 88, 97, 5, 0, 0, 13, 0, 0, 0, 185, 4, 69}', 'cost': 13, 'voltage': 1209, 'circuit': 1, 'chksum': 69}
2016-11-06 21:15:51,289 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484248, 'active_power': 1377, 'zerodata': False, 'source': '{164, 24, 225, 31, 88, 97, 5, 0, 0, 13, 0, 0, 0, 185, 4, 68}', 'cost': 13, 'voltage': 1209, 'circuit': 1, 'chksum': 68}
2016-11-06 21:15:51,298 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484252, 'active_power': 1377, 'zerodata': False, 'source': '{164, 28, 225, 31, 88, 97, 5, 0, 0, 13, 0, 0, 0, 185, 4, 72}', 'cost': 13, 'voltage': 1209, 'circuit': 1, 'chksum': 72}
2016-11-06 21:15:51,307 - query_ted - INFO - {"message": "duplicate key value violates unique constraint \"measurement_device_id_measurement_type_id_measurement_time_key\""}
2016-11-06 21:15:51,308 - query_ted - INFO - {'good_data': True, 'header': 164, 'timestamp': 1478484248, 'active_power': 1373, 'zerodata': False, 'source': '{164, 24, 225, 31, 88, 93, 5, 0, 0, 13, 0, 0, 0, 182, 4, 61}', 'cost': 13, 'voltage': 1206, 'circuit': 1, 'chksum': 61}
2016-11-06 21:15:51,312 - query_ted - INFO - {"message": "duplicate key value violates unique constraint \"measurement_device_id_measurement_type_id_measurement_time_key\""}


[pfletch101]

How are you ensuring that you don't download the same data twice? S for each download should, presumably, be one greater than the reported epoch time of the first (most recent) record in the last download. Bad things are likely to happen if you are assuming that the computer's and ECC's clocks are synchronized. I use a slightly different approach to polling, and I don't bother about second data, but I certainly haven't seen any duplicate timestamps in minute or hour data.

[SpaceGuy]

Thanks for the response.

I track the latest timestamp received, then on the next run this tracked timestamp is used to set the S parameter. Agree that this would cause a duplicate data or two, since the saved timestamp has already been downloaded.

But, I have a separate process that is downloading the second data every 30 minutes in raw format. When looking at a single file, there are many duplicates in this one file. See the attached files. The .raw.txt file is the base64 encoded messages. The .txt file shows the raw seconds, human readable time and all the data words from the message for this same raw file. There are a total of 3308 lines in the raw file. Of those messages, 620 are identified as duplicate. Looking at all the files for yesterday, consistently the number of duplicates per file is around 600. Consistently, nearly 10 minutes worth of data is duplicate data.

In some instances, all the data words are duplicate. In others, the data words after the time are different, i.e. active_power, cost and voltage are different.

I see similar issues for minute, hour and day. For the day data, I do have two days of the 7 days the system has been running that have 2 entries.

Any thoughts?

[pfletch101]

I see what you mean. As I have said, I do not download second data, but I did not think that I had seen this in the data for minutes, hours, and days that I do download. On reviewing my data records, however, I do see a (very) few duplicate values. The pattern of some of the duplicate minute values actually suggests a problem in my data validation and storage routines when I recover from rare program crashes, but some of the (again) very rare duplicate hour values are less easy to explain, other than on the basis of downloading duplicated data. One thought occurs to me: you are downloading a huge chunk of (second) data at a time by doing it every 30 minutes, and the ECC does not use a very high-end processor, so it may have problems keeping up with its current processing while doing the download. It might be interesting to see if your error rate goes down if you download the second data more frequently and/or if your error rate on the lower frequency data goes down if you temporarily stop downloading the batches of second data - do you really have a need for that degree of precision?