Database design

[tickett]

Hi gjelsvik, your path was very similar to mine... starting with power consumption monitoring then "getting the bug" and wanting to monitor and control EVERYTHING

I'm currently using the home automation hub software to process and post to pachube but want to take it a step further and store in my own SQL database (still undecided whether to use SQL Server or mySQL).

Would you all be willing to share your database/table designs rather than me re-inventing the wheel?

Thanks
Lee

[vanisher]

I'm also interested in the scriping

[Digit]

Here's my database; the script is based on MS SQL 2005.

[vanisher]

Thx!! Will look into it and trying to get in in mySQL.

[jennirivera]

I use one record for every hour otherwise I get to much record which is not important to me. It is simple, once a reading comes in I check if for that date/hour a record is in the database.

[Romac]

Bringing up this topic as I have been working on a historical database for my energy reading. I'm using Homeseer 3 to load the changed values to a database table in SQL Server and used the approach outlined in this topic. I have for each hour a record, starting at the beginning of the hour and updating until the next hour starts. This works well and I can find all readings in the database. I'm storing at the moment just the smart meter readings every 10s when the reading comes in. I'm planning in optimising this when everything is working well.

The issue I see is that I'm missing data when the hour changes. I liked to idea to have the start and end value for every hour, but when changing to the next hour I loose one interval. I can solve this by calculating the difference between the end valvues, but then loose the advantage of the start and end value every hour.

How have others solved this? What am I missing?

2014-03-13 07.57.21 pm.png (20.13 KiB) Viewed 23009 times

[Digit]

Since you got this far already, I hope that showing how I construct my queries with some (JS) code will suffice.
I changed some things so that fieldnames are like yours etc. but left out the LastUpdate field.

The newMStimestamp variable is always filled with values like:
"20140313 19:00:00.000"
"20140313 20:00:00.000"
"20140313 21:00:00.000"
etc., since I store them each hour and minutes, sec and msec don't matter so I set those to 0.

Code: Select all

      // MS SQL query
      
      sqlw = "DEVID = '"+deviceid.toUpperCase()+"' AND TimeStamp = CAST('"+newMStimestamp+"' AS DATETIME)";     
      q = "";
      q += "IF NOT EXISTS (SELECT * FROM DATA WHERE "+sqlw+") ";
      q += "INSERT INTO DATA (TimeStamp, DevID, StartValue, EndValue) ";
      q += "VALUES ('"+newMStimestamp+"'";
      q += ", '"+deviceid.toUpperCase()+"'";
      q += ","+prvValue.toString();
      q += ","+curValue.toString();
      q += ")";
      
      msqueryQueue.push(q);

      q = "";
      q += "UPDATE DATA SET EndValue = "+curValue.toString();
      q += "' WHERE "+sqlw;
      msqueryQueue.push(q);

The INSERT will only happen once every hour and the trick is to keep track of the previous value (prvValue), the value you got 10 seconds ago.
This is the end result in my MS SQL Server:

dbdKnipsel.PNG (16.36 KiB) Viewed 23003 times

[mlommers]

isn't it redundant to have for each hour the start and end value's? As the start value is equal to the end value of the previous hour?

[Romac]

Thanks Digit. This is much appreciated. I will have a look at your query and try to implement in my system. The query is definitely different and more complex then what I am running now.

[Digit]

isn't it redundant

Doing things this way is a bit of a trade-off, it's not necessary to store both values, that's correct.
But for example, a simple query result is instantly column chart ready.

And in fact it's not really redundant - that's when data is repeated in 2 or more tables (like copying a customers' VAT number to the sales order table) which is not the case here. In this case, they do have a different meaning.

[Bwired]

Thx!! Will look into it and trying to get in in mySQL.

this is the Mysql variant, i have much more fields (yes redundant i like), but discard that
the on duplicate is handy for mysql. discard also some nodejs lines like pushing the query
check also http://blog.bwired.nl/?p=1049

Code: Select all

var q = '';
    var s = (new Date()).toMySQLDateFormat() //+ '/'+ s;   
    var d = new Date();
    var n = d.getHours();
      q = "";
      q += "INSERT INTO smartmeter1 (datum, tijd, daguur, aantal, hour_consumed, hour_produced, consumed_rate1, consumed_rate2, produced_rate1, produced_rate2, actual_consumed, actual_produced, gas) ";
      q += "VALUES ('"+s+"'";
      q += ", '"+s+"'";
      q += ", '"+n.toString()+"'";
      q += ", '1'";
      q += ", '0'";
      q += ", '0'";
      q += ", '"+res.ConsumedPowerRate1+"'";
      q += ", '"+res.ConsumedPowerRate2+"'";
      q += ", '"+res.ProducedPowerRate1+"'";
      q += ", '"+res.ProducedPowerRate2+"'";
      q += ", '"+res.ActualConsumedPower+"'";
      q += ", '"+res.ActualProducedPower+"'";
      q += ", "+res.ConsumedGas;      
          q += ")";
          q += " ON DUPLICATE KEY UPDATE"
          q += " tijd='"+s+"',";
      q += " hour_consumed=hour_consumed+('"+res.ConsumedPowerRate1+"'-consumed_rate1)+('"+res.ConsumedPowerRate2+"'-consumed_rate2),";
      q += " hour_produced=hour_produced+('"+res.ProducedPowerRate1+"'-produced_rate1)+('"+res.ProducedPowerRate2+"'-produced_rate2),";
      q += " consumed_rate1='"+res.ConsumedPowerRate1+"',";
      q += " consumed_rate2='"+res.ConsumedPowerRate2+"',";
      q += " produced_rate1='"+res.ProducedPowerRate1+"',";
      q += " produced_rate2='"+res.ProducedPowerRate2+"',";
      q += " actual_consumed='"+res.ActualConsumedPower+"',";
      q += " actual_produced='"+res.ActualProducedPower+"',";
      q += " gas='"+res.ConsumedGas+"',";
          q += " aantal=aantal+1;";
     
    queryQueue.push(q);
    processQueryQueue();

[Romac]

It working!! I ended up doing it differently but the solutions posted here pointed me in the right direction.

For anybody struggling to get this to work, this is what I did (using HS3).

I run the below script every time a device value changes.

Code: Select all

'Log to Database for HS3
'WR. Muller
'08/03/2014
'Version: 1.0

'VB.Net script to write Homeseer 3 values to SQL Server Express Historical Database

'Add this line to Homeseer\Config\Settings.ini
'    ScriptingReferences=System.Data;System.Data.Dll

' Add this line to Homeseer\Scipts\Startup.ini
'    hs.RegisterStatusChangeCB("L2DH.vb","Main")

Imports System.Data.SqlClient

Sub Main(ByVal Parms As Object)

    Dim dev_address As String
    Dim device_valuenew, device_valueprv As Double
    Dim cn As New SqlConnection("Server=192.168.2.25,1433;Database=HS3Test;User ID=sa;Password=rm7800;Trusted_Connection=False;")

    hs.WriteLog("L2DH", "Script running")
    dev_address = Parms(1) 'address of device
    device_valuenew = Parms(2) 'new value of device
    device_valueprv = Parms(3) 'previous value of device

    Try
        cn.Open()
        hs.WriteLog("L2DB", "Connection is open")
        Dim SQLComm As New SqlCommand("UPDATE dbo.Data SET EndValue='" & device_valuenew & "' WHERE DevID='" & dev_address & "' AND DateDiff(hh, GetDate(),TimeStamp) = 0" & _
                                      "IF @@ROWCOUNT=0" & _
                                      "INSERT INTO dbo.Data(DevID, StartValue, EndValue) VALUES('" & dev_address & "','" & device_valueprv & "','" & device_valuenew & "')", cn)
        SQLComm.ExecuteNonQuery()
        hs.WriteLog("L2DH", "Logged to Historical Database: Address: " & dev_address & ", New Value: " & device_valuenew)
        cn.Close()
        hs.WriteLog("L2DH", "Connection is closed")
        'End If

    Catch ex As Exception
        hs.WriteLogEx("L2DH", "Error: " & ex.Message)
    End Try

End Sub

The database is set-up in SQLServer. The structure is like this:

Code: Select all

USE [HS3Test]
GO

/****** Object:  Table [dbo].[Data]    Script Date: 16-3-2014 8:19:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Data](
	[TimeStamp] [smalldatetime] NOT NULL,
	[LastUpdate] [datetime] NULL,
	[DevID] [nchar](15) NOT NULL,
	[StartValue] [float] NULL,
	[EndValue] [float] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Data] ADD  CONSTRAINT [DF_Data_timestamp]  DEFAULT (dateadd(hour,datediff(hour,(0),getdate()),(0))) FOR [TimeStamp]
GO

The constraint at the end is used to truncate the Timestamp value to a full hour. The way I have the INSERT statement written in the script, it always adds a new record when a new hour has started, by just deleting the minutes and seconds, you end up with the hour value.

The data looks like this when everything is working well.

2014-03-16 08.19.29 am.png (24.65 KiB) Viewed 22878 times

There are still a few things I need to do:
1. Make the logging device specific; now every device value change is logged
2. Making interval a device value is logged device specific. Now everything is logged at an interval of an hour which is fine for energy, but other devices may require something different.

[Romac]

Finally back on this topic and trying to make it more visable. Having now lots of data, I'm trying to make some nice graphs. For this I'm using Highcharts which is working quite well for data I can read directly from the database. I just struggle to setup a query to calculate the delta per day or per week. Can somebody help me in the right direction to get me going?

[Rene]

Have a look at influxdb.

[Romac]

Thanks Rene. At the moment all my data is in SQL Server and would like to stay with this solution. Just looking for a SQL query to group the data by day/week/month/year...


Sent from my iPhone using Tapatalk