douxuan1284
2017-04-05 11:30
浏览 59
已采纳

Google Annotatedtimeline数据被时间戳弄乱了

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],
";

                if($i > 1 or $i==x)
              {
                  $andmed.= "\t\t\t[new Date(" . $mod_date . ")," . $eelminelugem . ",undefined],
";
              }     

                  $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],
";

                $andmed2.= "\t\t\t[new Date(" . $mod_date . ")," . $roww["lugem"] .",undefined],
";

                $andmed2.= "\t\t\t[new Date(" . $uus_date. ")," . $roww["lugem"] .",undefined],
";

                $andmed2.= "\t\t\t[new Date(" . $uus_date . "),0,undefined],
";

                $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?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douwei2966 2017-04-05 16:18
    已采纳

    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
    
    打赏 评论

相关推荐 更多相似问题