CharlieParker wrote:

support wrote:How are you logging the temperature changes to SQLite currently? Are you using Trigger Actions that then execute a script?

No triggers, I run a shell script every 5 minutes to poll my devices and INSERT the current temperature readings into the SQLite db. I use php and JpGraph (a php library) to graph historical temps. Works for me, I'm just looking for other cool ways to use your cool software Thanks again.

Charlie,

Do you mind sharing some of your PHP code to query your SQLite db and pass this to JpGraph? I did a bit of playing few years ago with MySQL and JpGraph.... forgot all I learned

Dale

dalenis wrote:Do you mind sharing some of your PHP code to query your SQLite db and pass this to JpGraph?

Dale,

Here's my php code to draw a graph.

Code: Select all

/*
sqlite> .schema
CREATE TABLE Temps (tempOutside FLOAT, tempBasement FLOAT, time TIMESTAMP );
CREATE INDEX TempsX ON Temps (time);
*/
include ("jpgraph/jpgraph.php");
include ("jpgraph/jpgraph_line.php");

$daysToGraph = $_GET["GoBack"];
$end = "-"  . $daysToGraph . " days";

$db = sqlite_open('/path/to/db/temps.db', 0666, $sqliteerror);
$result = sqlite_query($db, "select * from Temps where time > datetime('now','localtime', '" . $end. "');");   

$i = 0;
$tp = 0;
while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
   $basementTemperatureArray[$i] = $row['tempBasement'];
   $outsideTempArray[$i] = $row['tempOutside'];
   
   // set up tick points - on the hour when graphing a single day
   //     at midnight if graphing more than one day
   $day = substr ($row['time'], 8, 2);
   $hour = substr ($row['time'], 11, 2);
   $minutes = substr ($row['time'], 14, 2);
   if ($daysToGraph == 1) {
      if ( $minutes == "00") {
            $tickpointPositions[$tp] = $i;
            $tickpointNames[$tp] = $hour;   
            $tp++;
      }
   }
   else {
      if (( $hour == "00") and ( $minutes == "00")){
            $tickpointPositions[$tp] = $i;
            $tickpointNames[$tp] = $day;
            $tp++;
      }      
   }
   $i++;   
}

$lineplotBasement=new LinePlot($basementTemperatureArray);
$lineplotBasement->SetColor("blue");
$lineplotOutside=new LinePlot($outsideTempArray);
$lineplotOutside->SetColor("red");

$graph = new Graph(850,280,"auto");   
$graph->SetScale("linlin", 0 , 0 , 0 , $i);
$graph->xaxis->SetTickPositions($tickpointPositions, null, $tickpointNames);
if ($daysToGraph > 1)
   $graph ->xgrid->Show(true);
$graph->Add($lineplotBasement);
$graph->Add($lineplotOutside);
$graph->Stroke();


I realize I'm a bit late responding; I haven't kept up with my RSS reader. But, I just wanted to pitch in my two cents.

I'm currently using the Indigo MySQL client, and unfortunately, I'm not going to be able to use Postgres, nor SQLite. It is integrated into an online web application, and I don't really want to spend all the time and effort to convert everything to a different database backend.

I guess I'll have to go back to using a script to parse the log file via a cron job.

dchote wrote:What about opening up support for sqlite. Pretty much every scripting language out there has support for sqlite. Not to mention its easy to throw together a core data app....

-Dan

The current beta of 2.5 can now log to SQLite.

Regards,
Matt

Matt

You mentioned in this thread, back on April 1 2008, that you were intending to add graphing of historical data to the client. Obviously, with the popularity of Venstar (Temperature monitoring/control) it would be very useful to be able see historical temperature data, thermostat on/off state (plus outside temperature). Do you intend to develop this, or do you have a suggestion for an alternative solution?

Yours, Rick

Here's my php code to draw a graph.

Cool, thnx for the code

Hi Rick,

I am interested in doing this, but it won't be making it into the next major version of Indigo. I did some experimenting with using the Google Chart API, but I'm not sure that I want to rely on an external charting engine. This is definitely an area where a 3rd party developer could create an Indigo Web Server plug-in which generates the chart based on the SQLite, PostgreSQL, or MySQL data and returns it via Indigo's Web Server.