Show non-uniformly time-spaced samples from MySQL database on Google Line Chart by php query

I’m using a Line Chart of Google, I take data from a database of MySQL, to show points of some variables at different datetime. The sample time is 1 minute, but ocationally some points are loss (dont worry about the reason), so when I create de chart, the distance between two point is the same for 1 minute or 1 hour. I want to the horizontal space between samples be consistent with the time jump.

The Date selection and Query to generate chart are:

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <select name="date_selected">
    <option value="">Seleccione:</option>
    <?php
      $available_dates=$connection->prepare("SELECT DISTINCT from_unixtime(ROUND(`time@timestamp`, 0),'%Y-%m-%d') as `Date` FROM `hostname_dbweintek_%m%d_data`");
      $available_dates=$available_dates->execute(array());
      while ($row_AvDate=$available_dates->fetch(PDO::FETCH_ASSOC)) {
        echo '<option value="'.$row_AvDate['Date'].'">'.$row_AvDate['Date'].'</option>';
      }
      $available_dates->closeCursor();
    ?>
  </select>
  <input type='submit' value="Generar">
</form>
</p>
</div>  

<?php
    if(isset($_POST["date_selected"])){
    $fecha=$_POST["date_selected"];
        //Where ejecuta antes que el alias "as `DateTime`", con lo cual: o se vuelve a escribir
        //todo el campo para el criterio "from_unixtime...", o se hace una subconsulta
    $query="select * from (SELECT 
                from_unixtime(ROUND(`time@timestamp`, 0),'%Y-%m-%d %H:%i') as `DateTime`, 
                CAST(data_format_0 AS DECIMAL(5,1))  as `Var0_MIN`,
                CAST(data_format_1 AS DECIMAL(5,1)) as `Var1_EXP`,
                CAST(data_format_2 AS DECIMAL(5,1)) as `Var2_SIN` 
            FROM 
                `hostname_dbweintek_%m%d_data`) s
            WHERE
                `DateTime` LIKE '$fecha%'";
    $result_rows=$connection->query($query)->fetchAll(PDO::FETCH_OBJ); //esto es un array de objetos    
?>

And the script:

<!-----------------LINE CHART-------------------->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
var data = google.visualization.arrayToDataTable([
['Fecha y hora', 'Var0_MIN', 'Var1_EXP', 'Var2_SIN'],
<?php
foreach($result_rows as $dato):
?>
['<?php echo $dato->DateTime ?>', <?php echo $dato->Var0_MIN ?>, <?php echo $dato->Var1_EXP ?>, <?php echo $dato->Var2_SIN ?>],
 <?php endforeach; ?>
]);

var options = {
    title: 'Resultado de la consulta de datos',
    curveType: 'none',
    axisTitlesPosition: 'out',
    'backgroundColor':{'fill':'#FFFFFF', 'stroke':'#F1F1F1', 'strokeWidth':10},
    chartArea: {'backgroundColor':'#F1F1F1'},
    fontSize: 12,
    fontName: 'Arial',
    hAxis:{title: 'Fecha y hora',
           titleTextStyle: {color: '#000000'}, 
           slantedText: false,
           slantedTextAngle: 60,
           minTextSpacing: 100},
    legend: {position: 'right', textStyle: {color: 'black', fontSize: 10}, alignment:'center', maxLines:5},
    lineWidth: 1,
    
    
    
};

var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));

chart.draw(data, options);
}
</script>

The final idea is to select one day and to show data from this day, so the X axis should be 24 hours.

Real data with non uniform spaced data

How it is showed in chart

Answer

the reason the axis labels aren’t spaced properly is because they are being loaded into the chart as strings

to get desired result, you will need to use actual date objects

this may be as easy as making the following change…

surround date string with new Date(), here…

<?php
foreach($result_rows as $dato):
?>
[new Date('<?php echo $dato->DateTime ?>'), <?php echo $dato->Var0_MIN ?>, <?php echo $dato->Var1_EXP ?>, <?php echo $dato->Var2_SIN ?>],
 <?php endforeach; ?>
]);

Advertisement

Answer