How to log temperature changes periodically?

[kbrady]

I am very happyWith the 2gig CT 30 thermostat. I haven't been able to output figure out how to log the temperatures periodically. I see there are commands to log data into the main log file. Is there a way to do this into another file? Any tips appreciated. Thank you in advance Kevin

[rmasonjr]

I dont know if it will help, but I just setup a recurring event that runs every 15 mins and log the temperatures to a SQL Server database. It requires a script that just grabs whatever temperature is in the virtual device and uses that value. If you want the script, let me know...

[Uncle Michael]

I see there are commands to log data into the main log file. Is there a way to do this into another file?

There's a free plug-in called 'Power Trigger' that will log values to a CSV file. It will also create simple graphs from the logged data. The plug-in actually does many other things too, which can make it a bit confusing on first encounter, but aside from being a bit quirky, it does work.

[Michael McSharry]

mcsTemperature, while also with a variety of capabilities, is primarily used to periodically log data from HS devices and then present the data is trend charts.

[cabbage]

rmasonjr,

I would like to have a look at that script if possible, can you use the data in graphs etc?

Cabbage

[rmasonjr]

Yes - the data is in the database, so you can graph it just about any way you want. It requires SQL Server Express, however, you can just change the connection string for other databases.

I will say that Michael's solution is much more out-of-the-box than my approach:

Imports System.Data.SqlClient

Public Const SENSOR_LIST As String = "#11,#18,#9,#14,#4,#7,#16,#22,Q16,Q11"
Public Const CONN As String = "Server=HOMESEER\SQLEXPRESS;Database=temperature;integrated security=SSPI;"

Public Sub Main(ByVal Parms As Object)
    Dim s As New SqlConnection
    Dim c As SqlCommand
    Dim sensors As String = SENSOR_LIST
    Dim ary() As String
    Dim i As Integer

    ary = Split(sensors, ",")
    s.ConnectionString = CONN
    Try
        s.Open()

        For i = 1 To UBound(ary)
            If (CDbl(hs.DeviceValue(ary(i))) > 0) Then
                ''ugh - Z-Wave temperature is xxxx whereas RFXCOM temperatures are xxx
                If (Left(ary(i), 1) <> "#") Then
                    c = New SqlCommand("INSERT INTO [temperature].[dbo].[temperature] ([device],[temperature],[dateTime]) " & _
                        "VALUES('" & ary(i) & "'," & CDbl(hs.DeviceValue(ary(i))) / 10.0 & ",getdate())", s)
                Else
                    c = New SqlCommand("INSERT INTO [temperature].[dbo].[temperature] ([device],[temperature],[dateTime]) " & _
                        "VALUES('" & ary(i) & "'," & CDbl(hs.DeviceValue(ary(i))) & ",getdate())", s)
                    '''" & hs.DeviceLastChange(ary(i)) & "'
                End If
                c.ExecuteNonQuery()
            End If
        Next i


    Catch ex As Exception
        hs.WriteLog("temperature", ex.Message)
    Finally
        s.Close()
        c = Nothing
        s = Nothing
    End Try
End Sub 

[kbrady]

Logging Temperatures

Thanks guysI will look into these. Much appreciated!


Kevin

[cabbage]

rmasonjr,

is there a way to produce graphs for HSTouch using the database?

I have a collection of scripts I have written I need to tie together, so I want to learn more about writing to databases, thanks for the example.

Cabbage

[rmasonjr]

rmasonjr,

is there a way to produce graphs for HSTouch using the database?

I have a collection of scripts I have written I need to tie together, so I want to learn more about writing to databases, thanks for the example.

Cabbage

Yes. Once the data is in the database, it's just a matter of what kind of graph you want to build. I use ChartDirector and here is one graph I build every :15 mins (you can display this in HSTouch with just a URL to the image):

Public Sub BuildGraphV2(ByVal parms As Object)
    Dim data0(999) As Double
    Dim data1(999) As Double
    Dim labels(999) As String
    Dim c As XYChart = New XYChart(300, 180, &HFFFFC0, &H0, 1)
    Dim layer As LineLayer
    Dim d As Date = Now()
    Dim i, hr As Integer
    Dim temperature, min, max As Double
    Dim s As New SqlConnection
    Dim cm As SqlCommand
    Dim sqlDr As SqlDataReader = Nothing

    Try
        s.ConnectionString = CONN
        s.Open()

        ''dataset0 - use its labels
        cm = New SqlCommand("select device,temperature,datetime,datepart(hour,datetime) as hour from temperature where device='#9' and datetime>=dateadd(hour,-24,getdate()) and datepart(minute,datetime)=0 order by datetime", s)
        sqlDr = cm.ExecuteReader
        While (sqlDr.Read)
            temperature = CDbl(sqldr(1)) / 10.0
            data0(i) = temperature
            If (temperature > max) Then
                max = temperature
            End If
            If (temperature < min Or min = 0) Then
                min = temperature
            End If
            hr = CInt(sqlDr(3))
            If (hr < 12 And hr > 0) Then
                labels(i) = CStr(hr) & "a"
            Else
                If (hr = 12) Then
                    labels(i) = "12p"
                Else
                    If (hr = 0) Then
                        labels(i) = "12a"
                    Else
                        labels(i) = CStr(hr - 12) & "p"
                    End If
                End If
            End If
            i += 1
        End While

        ReDim Preserve data0(i - 1)
        ReDim Preserve labels(i - 1)
        i = 0
        sqlDr.close()

        ''dataset1
        cm = New SqlCommand("select device,temperature,datetime,datepart(hour,datetime) as hour from temperature where device='#16' and datetime>=dateadd(hour,-24,getdate()) and datepart(minute,datetime)=0 order by datetime", s)
        sqlDr = cm.ExecuteReader
        While (sqlDr.Read)
            temperature = CDbl(sqldr(1)) / 10.0
            data1(i) = temperature
            If (temperature > max) Then
                max = temperature
            End If
            If (temperature < min Or min = 0) Then
                min = temperature
            End If

            i += 1
        End While

        ReDim Preserve data1(i - 1)

        c.setPlotArea(45, 35, 240, 120, &HFFFFFF, -1, -1, &HC0C0C0, -1)
        c.addLegend(45, 12, False, "", 8).setBackground(Chart.Transparent)
        c.addTitle("Temperature", "Arial Bold", 9, &HFFFFFF).setBackground(c.patternColor(New Integer() {&H4000, &H8000}, 2))
        c.yAxis().setLabelFormat("{value}")
        c.xAxis().setLabels(labels)
        layer = c.addLineLayer()
        layer.addDataSet(data0, &HCF4040, "Outside").setDataSymbol(Chart.SquareSymbol, 7)
        layer.addDataSet(data1, &H40CF40, "Wiring Closet").setDataSymbol(Chart.DiamondSymbol, 9)
        ''layer.setDataLabelFormat("{value|0}")
        c.xAxis().setLabelStep(5)
        c.yAxis.setLinearScale(min - 1, max)

        c.makeChart("C:\Program Files\HomeSeer 2\html\Images\temperature.jpg")
    Catch ex As exception
        hs.writelog("temperature", ex.message)
    Finally

        s.close()
    End Try

End Sub 

[TeleFragger]

It requires SQL Server Express, however, you can just change the connection string for other databases.

any specific version? 2005, 2008? im going to instal sql express... or sql lite... not sure as the dvd plugin they are trying to get going with sql lite (if memory serves me correct)...

[rmasonjr]

The scripts I use are all using SQL Server Express 2008. Over the years, I've used Access, MySQL and SQL Server. I might go SQLite at some point just to become more familiar with it.

Just FYI - SQL Server Express plays nicely alongside HomeSeer. Hardly any CPU Usage at all. In fact, the HomeSeer Speaker client uses more CPU:

[Michael McSharry]

Access is good for quick construction and small database sizes. I use SQL Server for my real data collection needs. I have done mySQL too, but just never had a need with SQL Server resident. SQLite is a little different animal as it does not have rich set of data types available with the others. Dates need particular attention. mcsTemperature has Access and SQL Server support. I have all four in mcsSprinklers and only SQLite in the plug computer version of mcsSprinklers.

[cabbage]

I am a complete novice when it comes to databases, could you give me a rough idea of what i need to setup before the posted scripts will do anything? I have installed SQL Express 2008.

Cabbage

[rmasonjr]

I am a complete novice when it comes to databases, could you give me a rough idea of what i need to setup before the posted scripts will do anything? I have installed SQL Express 2008.

Cabbage

If you havent already, download and install SQL Server Mgmt Studio Express.
Right-click on databases and create a new database called 'temperature'.
Run the following query to create the temperature table:
USE [temperature]
GO

/****** Object:  Table [dbo].[temperature]    Script Date: 10/11/2011 06:34:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[temperature](
    [temperature_id] [int] IDENTITY(1,1) NOT NULL,
    [device] [varchar](4) NULL,
    [temperature] [decimal](18, 0) NULL,
    [dateTime] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO 

[cabbage]

rmasonjr,

I have gone with an access database which I have successfully logged data in. I have tried your example with chart director, I am getting an error that type XYChart is not defined. I have Imports ChartDirector defined is there anything else I need.

by the way thanks for sharing.

Cabbage