Jon00 Database Charting Utility for Homeseer 3 & Homeseer 4

[DJF3]

What input method are you recording data into the database? Is the database updated via device value change [Timer=0], regular timer or manual?

I find this odd as I have made no changes to the internationalization code for over a year.

Database entry INI file

[DBTable12]
Name=Temp-Temperature1
[B]Data1[/B]=$dtnr:111||1
[B]Data1Label[/B]="Living"
[B]Data1Multiplier[/B]=
[B]Data2[/B]=$dtnr:112||1
[B]Data2Label[/B]="Meterkast"
[B]Data2Multiplier[/B]=
[B]Data3[/B]=$dtnr:113||1
[B]Data3Label[/B]="Niels"
[B]Data3Multiplier[/B]=
[B]Data4[/B]=$dtnr:114||1
[B]Data4Label[/B]="Bedroom"
[B]Data4Multiplier[/B]=
[B]Data5[/B]=$dtnr:221||1
[B]Data5Label[/B]="Bathroom"
[B]Data5Multiplier[/B]=
[B]Data6[/B]=$dtnr:222||1
[B]Data6Label[/B]="Office"
[B]Data6Multiplier[/B]=
[B]Data7[/B]=
[B]Data7Label[/B]=
[B]Data7Multiplier[/B]=
...etc...
[B]Timer[/B]=16
[B]PurgeDays[/B]=

data as shown in the Web interface (without chart ;-)


Chart Create INI entry

[Create1]
[B]DBTable[/B]=12
[B]ChartStyle[/B]=1
[B]ChartPalette[/B]=12
[B]TitleText[/B]=Temperature 1
[B]XAxisText[/B]=Date
[B]YAxisText[/B]=Temperature
[B]YAxisTextSec[/B]=
[B]AxisXFormat[/B]=HH:mm
[B]AxisYFormat[/B]=
[B]AxisYFormatSec[/B]=
[B]UseData1[/B]=1
[B]UseData2[/B]=1
[B]UseData3[/B]=1
[B]UseData4[/B]=1
[B]UseData5[/B]=1
[B]UseData6[/B]=1
[B]UseData7[/B]=1
[B]UseData8[/B]=0
[B]UseData9[/B]=0
[B]UseData10[/B]=0
[B]ChartTimeStart[/B]=1
[B]ChartTimeFinish[/B]=0
[B]ChartTimeFormat[/B]=M
[B]BaselineValue[/B]=0
[B]BaselineValueSec[/B]=Auto
[B]AxisYMaximum[/B]=Auto
[B]AxisYMaximumSec[/B]=Auto
[B]CreateVD[/B]=1
[B]AutoUpdate[/B]=1

I have also replaced all "-0" with "0" in one table but that did not make a difference.

Any other things I can test?

[jon00]

The issue is that the database is being populated with , rather than .

I don't understand how that can be happening because the last command is to replace , characters with . before being stored in the database.

It must be due to you parsing numbers from the device string and something is not working correctly.

Can you provide me with the full device string (including HTML code etc) for one of the devices in question?

I have to ask if you need to use this method?

[DJF3]

Problem solved....

I found out that while upgrading the DB Charting scrips to the latest 1.0.12 version, files in the bin/jon00, /script & /html folders were NOT updated.

After updating I immediately noticed the web-based charts working and the saved chart files working.

TIP: if you have similar problems, download the latest version and compare the file-size / dates with the ones you have in production.

[beerygaz]

Jon, does the plugin have the ability to plot maximum / average values for a time period? So for instance, could plot the maximum and average temp for a period, based on the temp data being captured from a device every 5 mins? I know I can add an "avg" & "max" label, but can I plot them over time?

[jon00]

No but I can take a look. It would need to read the database for the specific metric and time period, find the average or max value and use that as a means to add data back to the database.

Thinking about it, it may be one step behind.

[beerygaz]

So a mother database config with a data source of Max:database1:source1 or avg:database1:source2 and then use a timespan entry for the time (day, month, hour,etc)?


Sent from my iPad using Tapatalk HD

[jon00]

Just like you configure a data source for the database such as a device value, I was thinking about adding a new method which you would enter the following parameters:

DBtable number (1 upwards)
Field number (1 to 10)
Start time in minutes
End time in minutes
Max, Min or Avr (as a number 1, 2 or 3)

So it could be:

&dbq:1||4||5||0||1

So this would query database [DBTable1], field 4, return the maximum value within the time period of the last 5 minutes.

[beerygaz]

Yup that's what I was thinking and just articulated poorly. Add 'Total' too for counters and I think it would be an awesome enhancement.

[jon00]

Yes I'll add total as well.

[jon00]

Seems to be working. As I suspected, it is one step behind as it cannot write a metric and then read that same metric at the same time.

.

[jon00]

V1.0.13 released

Changes to this version:

Relating to Jon00DBCharting.ini and Jon00DBChartingDemo.ini:

Added Database entry method $qdb: (read previous data stored in the database and retrieve Maximum/Minimum/Average/Total value over a defined time span in minutes).

Relating to Jon00DBChartingCreate.ini and Jon00DBChartingCreateDemo.ini:

Added ShowAllValues setting. This allows charts to plot all values in the database for the time span (as it was prior to V1.0.13) or just changes in value which may result in fewer plots (datapoints).

Other:

You can now download table201.csv database file of the dynamic chart table data which can be imported into Excel etc.
Timeout on the dynamic web page increased.

[DJF3]

Dates messed up?

Team,

I had to do some manual changes to the DB when I noticed this:

The ORIGINAL DBTable41:


When using the Web Interface to display a Chart it will ONLY show data starting 8-12-2015 10:00:03, so data with the "new" date format. It will not show anything before that time, regardless of how I search or which dates I search.


Then I exported this table, modified the date formats (in the CSV file) and imported the result in DBTable55:



AGAIN, when using the Web Interface to display a Chart it will ONLY show data starting 8-12-2015 10:00:03 even though it appears as if the dates before 10:00:03 are fine..

The ERROR log shows: (right after displaying DBTable55 for the entire day 2015-12-08)

2015-12-08 21:13:48.764	Error in ReadDB Block 4: System.FormatException: String was not recognized as a valid DateTime.
   at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at System.Data.SQLite.SQLiteDataReader.GetValues(Object[] values)
   at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
   at System.Data.DataTable.Load(IDataReader reader)
   at Jon00DBChartingHS3.Form1.쟌딱ﯾʢꆃ(String ਕጣ덨䁸J, String 쓘≩㘰쩬Ȏ챐, String ਲ਼윅屝箬椾峀廓滌)
2015-12-08 21:13:48.780	Error in CreatePage Block 1: System.DivideByZeroException: Attempted to divide by zero.
   at System.Decimal.FCallDivide(Decimal& d1, Decimal& d2)
   at System.Decimal.Divide(Decimal d1, Decimal d2)
   at Jon00DBChartingHS3.Form1.푌ᯱ䂨䓠흇㆜楍捺(Object ﵭ牪영骍꒿梾왳)

Any idea?

[jon00]

I don't understand the question. Why are you changing the date formats in the first place? Manually updating the database is ripe for corruption issues.

[DJF3]

I don't understand the question. Why are you changing the date formats in the first place? Manually updating the database is ripe for corruption issues.

Well, to he honest I have no clue what happened there and why
1. the newly written data has a different format?
2. how the imported data's date format somehow changed?

One of the two happened but don't see the logic :-(

[jon00]

So is this all databases or just one? I cannot see how this can happen unless you were playing with regional settings.