Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
create a grid of temp values from DataLogDB
#1
I'm using the DataLogDB to trap temps, but the process has one row for each temp sensor. I currently have 5 sensors, but am adding at least 3 more. I might add another 3 based on the crazy values i'm seeing making me concerned the temperature problem is worse than I thought.

I use Excel as I don't need to see this via CQC, but its far easier if Excel has a 2 dimensional grid where a single row has the X values (time), and different columns have the Y value.

This CQLServer stored procedure (its basically ANSI so should work with minor changes in syntax with mySQL/etc).

I'm still learning how to automatically run this in SQLServer 2014 every hour, might have to resort to sqlcmd for that. (command line). Until then, you need to login to mgmt console to execute it, which sucks.

Overview:

1) Truncate the table (truncate means don't log the deletion, the master is the DataLogValues(
2) INSERT INTO (populate first column)
3-# of sensors) UPDATE (one update statement for each sensor)


I did this for temps, but you could do it for anything.

i'm leaving my values in here, but obviously you need to change the Descriptor='XXX' to whatever you put into the DataLogDB driver.

CREATE THE TABLE. I don't really have all these sensors, but null values are irrelevant, simpler to just create everything all at once.

Code:
Create table [dbo].[TemperatureGrid] (
[TimeStamp] [datetime],
[BRN] [int],
[Entry] [int],
[IVB] [int],
[Loft] [int],
[Thermostat] [int],
[AVB] [int],
[VXB] [int],
[Outside] [int],
[Attic] [int],
[DiningRoom] [int],
[LivingRoom] [int],
[Den] [int],
[KBath] [int],
[MBath] [int],
[Kitchen] [int],
[Basement] [int],
[ServerRoom] [int]) ;

2) Create the re-runnable stored procedure
Code:
CREATE PROCEDURE [dbo].[spHAPopulateGrid]
AS
BEGIN
SET NOCOUNT OFF;

truncate table HA.dbo.TemperatureGrid

INSERT INTO HA.dbo.TemperatureGrid (TimeStamp, BRN)
Select TimeStamp, Value
from HA.dbo.DataLogValues
where Descriptor='BRN_Temp'

update HA.dbo.TemperatureGrid
SET Entry=HA.dbo.DataLogValues.Value
FROM HA.dbo.DataLogValues
WHERE HA.dbo.DataLogValues.TimeStamp = HA.dbo.TemperatureGrid.TimeStamp
AND HA.dbo.DataLogValues.Descriptor='EntryTemp'

update HA.dbo.TemperatureGrid
SET IVB=HA.dbo.DataLogValues.Value
FROM HA.dbo.DataLogValues
WHERE HA.dbo.DataLogValues.TimeStamp = HA.dbo.TemperatureGrid.TimeStamp
AND HA.dbo.DataLogValues.Descriptor='IVBTemp'

update HA.dbo.TemperatureGrid
SET Loft=HA.dbo.DataLogValues.Value
FROM HA.dbo.DataLogValues
WHERE HA.dbo.DataLogValues.TimeStamp = HA.dbo.TemperatureGrid.TimeStamp
AND HA.dbo.DataLogValues.Descriptor='LoftTemp'

update HA.dbo.TemperatureGrid
SET Thermostat=HA.dbo.DataLogValues.Value
FROM HA.dbo.DataLogValues
WHERE HA.dbo.DataLogValues.TimeStamp = HA.dbo.TemperatureGrid.TimeStamp
AND HA.dbo.DataLogValues.Descriptor='Thermoheat'

END
------------------------------------
Devices I can't stand and wish I could replace: SmartThings, Hue, Concerto, VRUSB
My vlogs: https://www.youtube.com/c/IVBsHomeAutomation
Reply
#2
I'm no database expert but this looks like something you could do with a pivot select. If so, should give the same end result, just a different way.
--Kill all the serial ports--
Reply
#3
against the original DAtaLogValues? I didn't think Pivot Tables worked that way, don't they require a 2D table instead of multiple records in the same table.
------------------------------------
Devices I can't stand and wish I could replace: SmartThings, Hue, Concerto, VRUSB
My vlogs: https://www.youtube.com/c/IVBsHomeAutomation
Reply
#4
Alternatively you could use a query like this:

Code:
select tBRN.timestamp,
    tBRN.value as "BRN Temp",
    tEntry.value as "Entry Temp",
    tLoft.value as "Loft Temp"
from datalogvalues as tBRN,
    datalogvalues as tEntry,
    datalogvalues as tLoft
where tBRN.descriptor = "BRN"
   and (tEntry.timestamp = tBRN.timestamp and tEntry.descriptor = "Entry")
   and (tLoft.timestamp = tBRN.timestamp and tLoft.descriptor = "Loft")

Just make sure that your datalogvalues table is indexed on timestamp and descriptor.
(Oh, and also include a time range on tBRN.timestamp to narrow down how many rows the query returns)
Reply
#5
true, but a 3 way self-join in a few months of logging might have performance problems. And I'll have 8 sensors in a week or so, and 8 way self-join on a large table would definitely suck.
------------------------------------
Devices I can't stand and wish I could replace: SmartThings, Hue, Concerto, VRUSB
My vlogs: https://www.youtube.com/c/IVBsHomeAutomation
Reply
#6
true, but truncate and repopulating a temporary table using multiple updates over a few months of data might have performance problems....

What database server are you using? I assume MySQL?

If you happen to be using MS-SQL you could use a PIVOT query.

The other alternative is to use an update trigger on the datalogvalues table to update the pivot table real time.
Reply
#7
a truncate is instantaneous as there's no logging (vs a delete). And a self-join is always slower once you get past your 3rd self-join (I used to be a DBA on large systems). In this case especially so as the table will have non-related values to toss out (I'll be logging non-temp stuff).

Doesn't a pivot statement require a 2D table? I don't think you can run it on the DataLogValues as-is given that its flat, there's nothing to pivot against. (I think, I know how to pivot but i've only done it on 2D tables, which is what the query produces).
------------------------------------
Devices I can't stand and wish I could replace: SmartThings, Hue, Concerto, VRUSB
My vlogs: https://www.youtube.com/c/IVBsHomeAutomation
Reply


Possibly Related Threads...
Thread Author Replies Views Last Post
  Create a Customizable Dialog wuench 24 14,003 05-27-2015, 09:12 AM
Last Post: wuench
  How to create simple one way drivers Dean Roddey 2 2,879 05-03-2015, 12:14 PM
Last Post: Dean Roddey
  How to get the DataLogdb driver up and running Zanthic 20 18,158 01-17-2013, 03:20 PM
Last Post: Dean Roddey
  Create Custom DropDown Widget jrlewis 2 10,045 08-05-2011, 08:26 AM
Last Post: jrlewis
  How to create a "Run Time" counter and display beelzerob 13 8,722 01-12-2009, 08:06 AM
Last Post: beelzerob

Forum Jump:


Users browsing this thread: 1 Guest(s)