Kod: Markera allt
<h2>Värmesystem</h2>
<div id="placeholder" style="width:100%;height:450px;"></div>
<?php
mysql_connect("server", "username", "password");
mysql_select_db("database");
// Framledning
$query = "SELECT time, data FROM samples WHERE name='framledning_rad' AND time > NOW() - INTERVAL 1 DAY ORDER by time";
$result = @mysql_query ($query);
$framledning = array();
while($row = mysql_fetch_object($result))
$framledning[] = array((strtotime($row->time))*1000, $row->data);
// Utetemp
$query = "SELECT time, data FROM samples WHERE name='utetemp' AND time > NOW() - INTERVAL 1 DAY ORDER by time";
$result = @mysql_query ($query);
$utetemp = array();
while($row = mysql_fetch_object($result))
$utetemp[] = array((strtotime($row->time))*1000, $row->data);
// Börvärde
$query = "SELECT time, data FROM samples WHERE name='framledning_bv_rad' AND time > NOW() - INTERVAL 1 DAY ORDER by time";
$result = @mysql_query ($query);
$borvarde = array();
while($row = mysql_fetch_object($result))
$borvarde[] = array((strtotime($row->time))*1000, $row->data);
// Shunt
$query = "SELECT time, data FROM samples WHERE name='shunt' AND time > NOW() - INTERVAL 1 DAY ORDER by time";
$result = @mysql_query ($query);
$shunt = array();
while($row = mysql_fetch_object($result))
$shunt[] = array((strtotime($row->time))*1000, $row->data);
// Panna
$query = "SELECT time, data FROM samples WHERE name='panna' AND time > NOW() - INTERVAL 1 DAY ORDER by time";
$result = @mysql_query ($query);
$panna = array();
while($row = mysql_fetch_object($result))
$panna[] = array((strtotime($row->time))*1000, $row->data);
mysql_close();
?>
<script type="text/javascript">
$(function () {
var framledning = <?php echo json_encode($framledning); ?>;
var utetemp = <?php echo json_encode($utetemp); ?>;
var borvarde = <?php echo json_encode($borvarde); ?>;
var shunt = <?php echo json_encode($shunt); ?>;
var panna = <?php echo json_encode($panna); ?>;
$.plot($("#placeholder"),
[ { label: "Framledning [°C]", data: framledning },
{ label: "Börvärde [°C]", data: borvarde },
{ label: "Ute [°C]", data: utetemp },
{ label: "Panna [°C]", data: panna },
{ label: "Shunt [%]", data: shunt, yaxis: 2 } ],
{
xaxis: { mode: "time", timezone: "browser" },
yaxes: [ { tickFormatter: degreeFormatter }, { tickFormatter: percentFormatter, position: "right", min: 0, max: 100 } ],
legend: { position: "nw", backgroundColor: "gray" },
grid: { color: "white" }
}
);
});
</script>
Kod: Markera allt
function degreeFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" °C"; }
function percentFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" %"; }
function mibFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" MiB"; }
Kod: Markera allt
<script src="flot/jquery.js"></script>
<script src="flot/jquery.flot.js"></script>
<script src="flot/jquery.flot.time.js"></script>
Kod: Markera allt
mysql> select * from flot;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 5 |
+------+------+
3 rows in set (0.00 sec)
Kod: Markera allt
var dataset1 = '<?php echo json_encode($arr); ?>';
var dataset1 = "<?php echo json_encode($arr); ?>";
var dataset1 = \<?php echo json_encode($arr); ?\>;
var dataset1 = \<\?php echo json_encode($arr); \?\>;
Kod: Markera allt
<html>
<head>
<title>Flot Examples</title>
<script src="../jquery.js"></script>
<script src="../jquery.flot.js"></script>
<script src="../jquery.flot.time.js"></script>
</head>
<body>
<h1>Flot Examples</h1>
<div id="placeholder" style="width:600px;height:300px;"></div>
<?php
$db = mysql_connect("localhost", "user", "password");
mysql_select_db("onewire",$db);
$query = "SELECT x, y from flot";
$result = mysql_query($query,$db);
while($row = mysql_fetch_array($result))
{
$arr[] = array($row['x'],$row['y']);
}
?>
<script type="text/javascript">
$(function () {
var dataset1 = <?php echo json_encode($arr); ?>;
$.plot($("#placeholder"), [ dataset1 ] );
});
</script>
</body>
</html>
Kod: Markera allt
mysql> select unix_timestamp('2013-08-31 17:00:00')*1000;
+--------------------------------------------+
| unix_timestamp('2013-08-31 17:00:00')*1000 |
+--------------------------------------------+
| 1377961200000 |
+--------------------------------------------+
Skumt. Om man fyller i tiden du får på x-axeln i grafen och konverterar till unix time (http://www.onlineconversion.com/unix_time.htm)så får man: 1377960flyvert skrev: Är inte dessa tidsstämplar korrekta?
2013-08-31 17:00:00 = 1377961200000
2013-08-31 18:00:00 = 1377964800000
Jag klippte faktiskt in en snutt på vad PHP skriptet returnerar:ekdahl skrev:Skumt. Om man fyller i tiden du får på x-axeln i grafen och konverterar till unix time (http://www.onlineconversion.com/unix_time.htm)så får man: 1377960flyvert skrev: Är inte dessa tidsstämplar korrekta?
2013-08-31 17:00:00 = 1377961200000
2013-08-31 18:00:00 = 1377964800000
Ta sedan det gånger 1000 så har du 1377960000. Det är tre siffror färre än vad det borde vara. Är du säker på att det i json-datan blir 13 siffror? Har du en länk där man kan se den genererade koden?
Jag menade en länk där man kan se hela den genererade sidan.flyvert skrev:Jag klippte faktiskt in en snutt på vad PHP skriptet returnerar:
"label": "Inomhustemp",
"data": [[1377961200000,22.017],[1377964800000,22.1021]...
Ja, eftersom flot använder sig av javascript timestamps, millisekunder sedan 1970.Slänger man siffrorna direkt i onlineconversion får man: 1377961200000 -> Thu, 8 Nov 45635 00:00:00 UTC
Det jag försökte säga var att flot beter sig som att det får unix timestamps istället för javascript timestamps, dvs tre nollor för lite. Jag kan inte se var felet kan ligga med det du har klippt in, så om vi kan få se hela sidan skulle det underlätta.Så varför blir det 1970 i flot?
Har du lust att lägga ut exempel för din lösning? Jag har testat att grafa lite från min MySQL-databas men inte nått någon större framgång ännu.flyvert skrev:Äntligen lite frukt från allt slit!
Fick till slut ordning på det mesta - dom felaktiga tidsstämplarna får jag inte längre fram idag - tror att IE / MS Java buggar ur ibland - startar man om IE kan fel försvinna lika fort som dom dök upp. T.ex. ser jag ibland i Apache-loggarna att IE inte skickar någon databegäran när man laddar en sida, inte ens på ny tab, men startar jag om IE om börjar den hämta data igen. Kanske något med nån cachefunktion som jag inte begripit mig på?
Är mycket tacksam för all hjälp jag fått - och känner mig faktiskt glad att jag "tvingades in" på AJAX när PHP anropen från Java inte fungerade av någon besynnerlig anledning. Med AJAX i botten kan man lätt fixa uppdatering av kurvan i realtid samt lägga in knappar för zoom, panorering, val av data, statistikfunktion, etc. Skall nog försöka lägga in mer funktioner framöver o se vad man kan uppnå. Närmast på tapeten står dock nog att få elförbrukningsgivaren på plats på 1wire nätet.
Tack för denna gång!
Absolut (när kvällsmaten är i magen, barnen i sängarna, huset blivit städat och skafferiet påfyllt) - kolla tidsstämplarna på mina inlägg så ser du när jag får tid för detta...hellgun skrev:Har du lust att lägga ut exempel för din lösning? Jag har testat att grafa lite från min MySQL-databas men inte nått någon större framgång ännu.
Ja, ta det om/när du har tid.flyvert skrev: Absolut (när kvällsmaten är i magen, barnen i sängarna, huset blivit städat och skafferiet påfyllt) - kolla tidsstämplarna på mina inlägg så ser du när jag får tid för detta...
Då är vi på samma nivåflyvert skrev: Sen måste jag nog oxo slänga in en brasklapp så att mina första tafatta trevanden i PHP o Java inte skall tas som "referensmaterial" o "best practise"...
Kod: Markera allt
mysql> desc data;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| time | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sensorid | smallint(6) | NO | | NULL | |
| property | smallint(6) | NO | | NULL | |
| value | float | NO | | NULL | |
| aggregate | smallint(6) | NO | | NULL | |
+-----------+-------------+------+-----+-------------------+-----------------------------+
Kod: Markera allt
mysql> desc sensors;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sensorid | smallint(6) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| onewireid | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.01 sec)
mysql> desc properties;
+------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-----------------------------+
| time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| propertyid | smallint(6) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| enhet | varchar(4) | YES | | NULL | |
| sensorid | varchar(15) | NO | | NULL | |
+------------+-------------+------+-----+-------------------+-----------------------------+
Kod: Markera allt
#!/bin/bash
sens1temp=$(cat /mnt/1wire/7E.C42600001000/EDS0068/temperature)
sens1humi=$(cat /mnt/1wire/7E.C42600001000/EDS0068/humidity)
sens1lite=$(cat /mnt/1wire/7E.C42600001000/EDS0068/light)
sens1pres=$(cat /mnt/1wire/7E.C42600001000/EDS0068/pressure)
# Central heating, outgoing
sens2temp=$(cat /mnt/1wire/28.6E5495040000/temperature)
# District heating, incoming
sens3temp=$(cat /mnt/1wire/28.3083D6020000/temperature)
mysql --user=user --password=password << ENDSQL
use onewire
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 1, $sens1temp, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 2, $sens1humi, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 3, $sens1lite, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 4, $sens1pres, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 2, 1, $sens2temp, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 3, 1, $sens3temp, 1 );
ENDSQL
Kod: Markera allt
USE onewire
DROP PROCEDURE IF EXISTS updatehourdata;
DELIMITER //
CREATE PROCEDURE updatehourdata()
BEGIN
DECLARE thissensor SMALLINT;
DECLARE thisproperty SMALLINT;
DECLARE thistime TIMESTAMP;
DECLARE thisvalue FLOAT;
DECLARE sensorcur CURSOR FOR select sensorid from sensors order by sensorid;
DECLARE propertycur CURSOR FOR select propertyid from properties where sensorid = thissensor order by propertyid;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE sensorcur;
OPEN sensorcur;
REPEAT
FETCH sensorcur INTO thissensor;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE propertycur;
OPEN propertycur;
REPEAT
FETCH propertycur INTO thisproperty;
-- Find most recent hourvalue
SELECT MAX(time) + interval 1 hour INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 2;
IF ISNULL(thistime) THEN
-- No previous hourvalue found -> fallback on oldest minutevalue
SELECT min(time) + interval 1 hour INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1;
END IF;
-- Check if still NULL -> there is no data available -> do not proceed with calculating any averages.
IF NOT ISNULL(thistime) THEN
-- Shave off any minutes or seconds
SET thistime = DATE_SUB(thistime, interval minute(thistime) minute);
SET thistime = DATE_SUB(thistime, interval second(thistime) second);
-- Thistime does now point to next hourly average to compute
WHILE thistime < NOW() DO
-- Calculate an hourly average for this sensor, property and time frame.
SELECT AVG(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND time >= thistime - interval 1 hour AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( thistime, thissensor, thisproperty, thisvalue, 2 );
-- Increment hour
SET thistime = DATE_ADD(thistime, interval 1 hour);
END WHILE;
END IF;
UNTIL false END REPEAT;
CLOSE propertycur;
END;
UNTIL false END REPEAT;
CLOSE sensorcur;
END //
Kod: Markera allt
USE onewire
DROP PROCEDURE IF EXISTS updatedaydata;
DELIMITER //
CREATE PROCEDURE updatedaydata()
BEGIN
DECLARE thissensor SMALLINT;
DECLARE thisproperty SMALLINT;
DECLARE thistime TIMESTAMP;
DECLARE peaktime TIMESTAMP;
DECLARE thisvalue FLOAT;
DECLARE sensorcur CURSOR FOR select sensorid from sensors order by sensorid;
DECLARE propertycur CURSOR FOR select propertyid from properties where sensorid = thissensor order by propertyid;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE sensorcur;
OPEN sensorcur;
REPEAT
FETCH sensorcur INTO thissensor;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE propertycur;
OPEN propertycur;
REPEAT
FETCH propertycur INTO thisproperty;
-- Find most recent dayvalue
SELECT MAX(time) + interval 1 day INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 3;
IF ISNULL(thistime) THEN
-- No previous dayvalue found -> fallback on oldest hourvalue
SELECT min(time) + interval 1 day INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 2;
END IF;
-- Check if still NULL -> there is no data available -> do not proceed with calculating any averages.
IF NOT ISNULL(thistime) THEN
-- Shave off any hours, minutes and seconds
SET thistime = DATE_SUB(thistime, interval hour(thistime) hour);
SET thistime = DATE_SUB(thistime, interval minute(thistime) minute);
SET thistime = DATE_SUB(thistime, interval second(thistime) second);
-- Thistime does now contain next daily average to compute
WHILE thistime < NOW() DO
-- Calculate an daily average for this sensor, property and time frame.
SELECT AVG(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 2
AND time >= thistime - interval 1 day AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( thistime, thissensor, thisproperty, thisvalue, 3 );
-- Find day low value
SELECT MIN(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND time >= thistime - interval 1 day AND time < thistime;
-- Find first occurrence of day low value
SELECT MIN(time) INTO peaktime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND value = thisvalue AND time >= thistime - interval 1 day AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( peaktime, thissensor, thisproperty, thisvalue, 4 );
-- Find day high value
SELECT MAX(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND time >= thistime - interval 1 day AND time < thistime;
-- Find first occurrence of day high value
SELECT MIN(time) INTO peaktime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND value = thisvalue AND time >= thistime - interval 1 day AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( peaktime, thissensor, thisproperty, thisvalue, 5 );
-- Increment one day
SET thistime = DATE_ADD(thistime, interval 1 day);
END WHILE;
END IF;
UNTIL false END REPEAT;
CLOSE propertycur;
END;
UNTIL false END REPEAT;
CLOSE sensorcur;
END //
Kod: Markera allt
* * * * * /usr/local/bin/loggatemp > /tmp/loggatemp.log 2>&1
0 * * * * /usr/bin/mysql --user=user --password=password -e'call updatehourdata;' onewire > /tmp/updatehourdata.log 2>&1
1 0 * * * /usr/bin/mysql --user=user --password=password -e'call updatedaydata;' onewire > /tmp/updatedaydata.log 2>&1
Kod: Markera allt
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Signaler från EDS0068</title>
<link href="../examples.css" rel="stylesheet" type="text/css">
<!--[if lte IE 8]><script language="javascript" type="text/javascript" src="../../excanvas.min.js"></script><![endif]-->
<script language="javascript" type="text/javascript" src="../../jquery.js"></script>
<script language="javascript" type="text/javascript" src="../../jquery.flot.js"></script>
<script language="javascript" type="text/javascript" src="../../jquery.flot.time.js"></script>
<script type="text/javascript">
$(function() {
function degreeFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" °C"; }
function percentFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" %"; }
function hPaFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" hPa"; }
function luxFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" lux"; }
var options = {
lines: { show: true },
xaxis: { mode: "time",
timeformat: "%a-%d %H:%M" },
yaxes: [ { tickFormatter: degreeFormatter },
{ tickFormatter: percentFormatter },
{ tickFormatter: hPaFormatter, position: "right" },
{ tickFormatter: luxFormatter, position: "right" } ] };
var data = [];
function onDataReceived(series) {
data.push(series);
$.plot("#placeholder", data, options);
}
$.ajax({url: "rumstemp.php", type: "GET", dataType: "json", success: onDataReceived });
$.ajax({url: "luftfuktighet.php", type: "GET", dataType: "json", success: onDataReceived });
$.ajax({url: "ljusstyrka.php", type: "GET", dataType: "json", success: onDataReceived });
$.ajax({url: "lufttryck.php", type: "GET", dataType: "json", success: onDataReceived });
});
</script>
</head>
<body>
<div class="demo-container">
<div id="placeholder" class="demo-placeholder"></div>
</div>
</body>
</html>
Kod: Markera allt
<?php
$db = mysql_connect("localhost", "user", "password");
mysql_select_db("onewire",$db);
$query = "SELECT UNIX_TIMESTAMP(time)*1000 \"time\", value from data where sensorid=1 and property=1 and aggregate=2 order by time";
$result = @mysql_query ($query);
$arr = array();
while($row = mysql_fetch_object($result))
{
$arr[] = array($row->time*1,$row->value*1);
}
echo "{\n\"label\": \"Inomhustemp (°C)\",";
echo "\n\"data\": ";
echo json_encode($arr);
echo ",\n\"color\": 0,\n";
echo "\"yaxis\": 1\n}\n";
mysql_close();
?>