php - Google Annotatedtimeline 数据被时间戳搞乱了

Question

我使用谷歌注释时间线作为监控传感器的设备。我正在使用 mqtt python 脚本将数据发送到 MYSQL 数据库。所以我从数据库收到我的时间线数据。我有单独的传感器流量传感器和仪表传感器，我希望它们都显示在时间轴上，所以我使用 datajoin。问题是其中一个传感器时间线开始较早或较晚，如下所示:

如您所见，蓝线比红线晚开始。具体来说，红色是仪表，蓝色是流量。

现在，我的数据如下所示:

    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);                           
      }

通过使用这样的 SQL 查询:

  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

以及如何使用 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++; 

                }


        }

我的时间戳应该有问题，因为如果我在 phpmyadmin 上执行查询，它总是给出比预期早开始或结束的传感器时间戳值。 Soo..我的 sql 查询有问题还是我没有正确执行其他操作？

Answer 1

建议将两个 sql 语句合并为一个，
这将防止返回不同范围的时间戳

尝试如下操作...

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

或者您可能想要聚合...

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