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
Advertisement
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; ?> ]);