Database design
Re: Database design
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
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
Re: Database design
Here's my database; the script is based on MS SQL 2005.
- Attachments
-
- script.rar
- (3.35 KiB) Downloaded 922 times
-
- Starting Member
- Posts: 1
- Joined: Tue May 28, 2013 8:20 am
Re: Database design
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.
Re: Database design
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?
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?
HS3, Z-Wave Network, CommandFusion GUI
Re: Database design
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.
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:
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);
This is the end result in my MS SQL Server:
Re: Database design
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?
Re: Database design
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.
HS3, Z-Wave Network, CommandFusion GUI
Re: Database design
Doing things this way is a bit of a trade-off, it's not necessary to store both values, that's correct.mlommers wrote:isn't it redundant
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.
Re: Database design
this is the Mysql variant, i have much more fields (yes redundant i like), but discard thatvanisher wrote:Thx!! Will look into it and trying to get in in mySQL.
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();
Re: Database design
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.
The database is set-up in SQLServer. The structure is like this:
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. 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.
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
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 data looks like this when everything is working well. 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.
HS3, Z-Wave Network, CommandFusion GUI
Re: Database design
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?
HS3, Z-Wave Network, CommandFusion GUI
Re: Database design
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
Sent from my iPhone using Tapatalk
HS3, Z-Wave Network, CommandFusion GUI