phpmysqlchartsgoogle-visualizationannotatedtimeline
Google Annotatedtimeline data is messed up by timestamp
I'm using google annotatedtimeline as an device for monitoring sensors. I'm using mqtt python scripts for sending the data to MYSQL database. And so I receive my timelinedata from database. I have separate sensors flow sensor and meter sensor, I want them both to display on timeline, so I use datajoin. The problem is that one of the sensors timeline start earlier or later, like this:
As you see the blue line starts later than the red one. As specific, red is the meter and blue is flow.
Now, my data looks like this:
function drawChart()
{
var data = new google.visualization.DataTable();
data.addColumn('datetime', 'Aeg');
data.addColumn('number', 'Vooluandur C200');
data.addColumn('string', 'Nimi');
data.addRows([
[new Date(2017,3,05,14,10,48),10,undefined],
[new Date(2017,3,05,14,10,47),9,undefined],
[new Date(2017,3,05,14,10,47),10,undefined],
[new Date(2017,3,05,14,10,46),10,undefined],
[new Date(2017,3,05,14,10,46),9,undefined],
[new Date(2017,3,05,14,10,43),11,undefined],
[new Date(2017,3,05,14,10,43),10,undefined],
[new Date(2017,3,05,14,10,42),9,undefined],
[new Date(2017,3,05,14,10,42),11,undefined],
[new Date(2017,3,05,14,10,41),8,undefined],
[new Date(2017,3,05,14,10,41),9,undefined],
[new Date(2017,3,05,14,10,39),10,undefined],
[new Date(2017,3,05,14,10,39),8,undefined],
[new Date(2017,3,05,14,10,38),11,undefined],
[new Date(2017,3,05,14,10,38),10,undefined],
[new Date(2017,3,05,14,10,37),10,undefined],
[new Date(2017,3,05,14,10,37),11,undefined],
[new Date(2017,3,05,14,10,36),9,undefined],
[new Date(2017,3,05,14,10,36),10,undefined]
]);
var data2 = new google.visualization.DataTable();
data2.addColumn('datetime', 'Aeg');
data2.addColumn('number', 'Kooder');
data2.addColumn('string', 'Nimi');
data2.addRows([
[new Date(2017,3,05,14,10,48),0,undefined],
[new Date(2017,3,05,14,10,48),1,undefined],
[new Date(2017,3,05,14,10,48),1,undefined],
[new Date(2017,3,05,14,10,48),0,undefined],
[new Date(2017,3,05,14,10,47),0,undefined],
[new Date(2017,3,05,14,10,47),1,undefined],
[new Date(2017,3,05,14,10,47),1,undefined],
[new Date(2017,3,05,14,10,47),0,undefined],
[new Date(2017,3,05,14,10,45),0,undefined],
[new Date(2017,3,05,14,10,45),1,undefined],
[new Date(2017,3,05,14,10,45),1,undefined],
[new Date(2017,3,05,14,10,45),0,undefined],
[new Date(2017,3,05,14,10,43),0,undefined],
[new Date(2017,3,05,14,10,43),1,undefined],
[new Date(2017,3,05,14,10,43),1,undefined],
[new Date(2017,3,05,14,10,43),0,undefined],
[new Date(2017,3,05,14,10,42),0,undefined],
[new Date(2017,3,05,14,10,42),1,undefined],
[new Date(2017,3,05,14,10,42),1,undefined],
[new Date(2017,3,05,14,10,42),0,undefined],
[new Date(2017,3,05,14,10,40),0,undefined],
[new Date(2017,3,05,14,10,40),1,undefined],
[new Date(2017,3,05,14,10,40),1,undefined],
[new Date(2017,3,05,14,10,40),0,undefined],
[new Date(2017,3,05,14,10,38),0,undefined],
[new Date(2017,3,05,14,10,38),1,undefined],
[new Date(2017,3,05,14,10,38),1,undefined],
[new Date(2017,3,05,14,10,38),0,undefined],
[new Date(2017,3,05,14,10,37),0,undefined],
[new Date(2017,3,05,14,10,37),1,undefined],
[new Date(2017,3,05,14,10,37),1,undefined],
[new Date(2017,3,05,14,10,37),0,undefined],
[new Date(2017,3,05,14,10,35),0,undefined],
[new Date(2017,3,05,14,10,35),1,undefined],
[new Date(2017,3,05,14,10,35),1,undefined],
[new Date(2017,3,05,14,10,35),0,undefined],
[new Date(2017,3,05,14,10,33),0,undefined],
[new Date(2017,3,05,14,10,33),1,undefined],
[new Date(2017,3,05,14,10,33),1,undefined],
[new Date(2017,3,05,14,10,33),0,undefined]
]);
var joinedData = google.visualization.data.join(data, data2, 'full', [[0, 0]], [1], [1]);
var options = {
thickness: 3, displayExactValues:true, displayAnnotations:true
};
var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('d3x'));
chart.draw(joinedData, options);
}
By using SQL queries like this:
SELECT r.id,r.device_id as device,r.sensor_id as sensor,r.timestamp as aeg2,r.timestamp as aeg,r.value_int as lugem from readings r
where r.sensor_id="2"
ORDER BY r.id desc limit 10
SELECT timestamp as aeg,value_int as lugem,value_string as lugem2,r.device_id as device,r.timestamp as aeg2,r.id,r.sensor_id as sensor
FROM readings r
WHERE r.sensor_id="1" order by r.id desc limit 10
And how I edit the data with php:
foreach ($result as $row ){
$i++;
$date = date("Y,m,d,H,i,s", strtotime($row["aeg"]));
$mod_date = substr_replace($date, intval(substr($date, 5,2))-1, 5,2) ;
if($andur=="1" or $andur=="1, 2"){
$andmed.= "\t\t\t[new Date(" . $mod_date . ")," . $row["lugem"] .",undefined],\n";
if($i > 1 or $i==x)
{
$andmed.= "\t\t\t[new Date(" . $mod_date . ")," . $eelminelugem . ",undefined],\n";
}
$eelminelugem = $row["lugem"];
$tootlikkus+= $row["lugem"];
if ($row["lugem"]<10) $seisuaeg++;
}
}
foreach ($resultt as $roww){
$datee2= date("Y,m,d,H,i,s",strtotime($roww["aeg2"]) +0.9);
$datee = date("Y,m,d,H,i,s", strtotime($roww["aeg2"]));
// Google Viz bugi, et kuu väärtused algavad 0st mitte 1-st.
$mod_date = substr_replace($datee, intval(substr($datee, 5,2))-1, 5,2) ;
$uus_date = substr_replace($datee2, intval(substr($datee2, 5,2))-1, 5,2) ;
if($andur=="2" or $andur=="1, 2"){
$andmed2.= "\t\t\t[new Date(" . $mod_date . "),0,undefined],\n";
$andmed2.= "\t\t\t[new Date(" . $mod_date . ")," . $roww["lugem"] .",undefined],\n";
$andmed2.= "\t\t\t[new Date(" . $uus_date. ")," . $roww["lugem"] .",undefined],\n";
$andmed2.= "\t\t\t[new Date(" . $uus_date . "),0,undefined],\n";
$tootlikkus+= $roww["lugem"];
if ($roww["lugem"]<10) $seisuaeg++;
}
}
There should be a problem with my timestamp, because if I executed query on phpmyadmin, It allways gave sensor timestamp values that started or ended earlier than expected. Soo.. Is there a problem with my sql query or I'm not doing it right with something else?
Solution
recommend combining the two sql statements in one,
this will prevent returning different ranges of timestamps
try something like the following...
SELECT
r.timestamp as aeg,
r.id,
r.device_id as device,
r.sensor_id as sensor,
CASE WHEN
r.sensor_id = "1"
THEN
r.value_int
ELSE
0
END as lugem1,
CASE WHEN
r.sensor_id = "2"
THEN
r.value_int
ELSE
0
END as lugem2
FROM
readings r
ORDER BY
r.id desc
LIMIT 10
or you may want to aggregate...
SELECT
r.timestamp as aeg,
r.id,
r.device_id as device,
r.sensor_id as sensor,
SUM (
CASE WHEN
r.sensor_id = "1"
THEN
r.value_int
ELSE
0
END
) as lugem1,
SUM (
CASE WHEN
r.sensor_id = "2"
THEN
r.value_int
ELSE
0
END
) as lugem2
FROM
readings r
GROUP BY
r.timestamp,
r.id,
r.device_id,
r.sensor_id
ORDER BY
r.id desc
LIMIT 10