CODEWITHSUNDEEP
phpmysqlchartsgoogle-visualizationannotatedtimeline
Somepub
Somepub

Reputation: 435

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:

enter image description here

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?

Upvotes: 0

Views: 45

Answers (1)

WhiteHat
WhiteHat

Reputation: 61232

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

Upvotes: 0

Related Questions