MySQL DateTime - XAXIS HIGHCHART

Question

I'm new in this community. Since a few days I'm trying to implement a chart using Highstock. This chart will display the monitoring of temperatures in function of the time.

The values are acquired through PHP and stored in MySQL. I am able to display the temperatures values but not the DateTime in the XAXIS of Highchart.

I am not used with the Javascript and I think my problem could be issued if I find the solution how to convert DateTime into Timestamp in Javascript and displays it as DateTime.

Please find my code below :

    <?php

        //Récupération des valeurs stockées dans MySQL
        mysql_connect("localhost","root","root");       //connexion à la base de données mysql
        mysql_select_db("Chart");                       //connexion à la base de données concernées

?>

<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script src="https://code.highcharts.com/stock/highstock.js"></script>
<script src="https://code.highcharts.com/stock/modules/exporting.js"></script>


<div id="container">

<script>

<?php
        //récuparation de la colonne "value" dans le tableau "temperature"
        $query = mysql_query("SELECT value FROM inside_temp");
        while($ligne = mysql_fetch_array($query)){
                $inside_temp[] = $ligne[0];
        }
        //récupération de la colonne date dans le tableau date
        $query = mysql_query("SELECT date FROM inside_temp");
        while($row = mysql_fetch_array($query)){
                //$date_inside[] = $row[0];
                $date_inside_temp[] = strtotime($row[0]) *1000;
                $date_inside_temp[] = date_format($row[0]," Y-m-d H:i:s,");
        }
        //récuparation de la colonne "value" dans le tableau "temperature"
        $query = mysql_query("SELECT value FROM outside_temp");
        while($ligne = mysql_fetch_array($query)){
                $outside_temp[] = $ligne[0];
        }
        //récuparation de la colonne "value" dans le tableau "temperature"
        $query = mysql_query("SELECT value FROM thermostat_setpoint");
        while($ligne = mysql_fetch_array($query)){
                $therm_setpoint[] = $ligne[0];
        }
        //récuparation de la colonne "value" dans le tableau "temperature"
        $query = mysql_query("SELECT value FROM consumption");
        while($ligne = mysql_fetch_array($query)){
                $consumption[] = $ligne[0] / 1000;
        }

?>

    Highcharts.stockChart('container', {

        rangeSelector: {
            selected: 4
        },

        title: {
                text: 'Consumption & Temperature monitoring'
        },

        subtitle: {
                text: 'Heating experiment - LTU, Summer 2017'
        },

        xAxis: {
                type: 'datetime',
                        //categories: [<?php echo join($date_inside_temp,',') ?>],
                        categories: Date.UTC(<?php echo $date_inside_temp;?>),
                tickInterval: 15,
                labels: {
                        format: '{value:%Y-%b-%e %H:%m}'
                }
        },

        yAxis: {
                title: {
                        text: 'Measured values (°C or kW)'
                        }
        },


        series: [{
                name: 'Wall plug consumption',
                data:[<?php echo join($consumption,',') ?>]

        }, {

                name: 'Inside temperature',
                data: [<?php echo join($inside_temp,',') ?>]

        }, {

                name: 'Thermostat setpoint',
                data:[<?php echo join($therm_setpoint,',') ?>]

        }, {

                name: 'Outside temperature',
                data:[<?php echo join($outside_temp,',') ?>]

     }] //fin de series

    });

The current result in picture

Answer 1

For highcharts you must convert dateTime not only into timestamp, but in timestamp with milliseconds.

When i had such a problem. I converted dateTime to timeStamp with milliseconds in PHP. It is convenient to do in PHP.

PHP

strtotime({your_dateTime})*1000

MYSQL This Query for CONVERT DATETIME to UNIX TIME STAMP

SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))*1000

But if you want to do conversion in JavaScript ONLY, so:

converting-a-datetime-string-to-timestamp-in-javascript

how-do-you-get-a-timestamp-in-javascript

---

06.08.2017 - update

Official docs: data-from-a-database How to fetch data from mysql and convert to to highcharts.

Convertation in php sandbox : php_sand_box

jsFiddle http://jsfiddle.net/1gbpzeho/

Answer 2

Refer to Highcharts API on series.data.x

x: Number

The x value of the point. For datetime axes, the X value is the timestamp in milliseconds since 1970.

In your code you first convert DateTime to milliseconds

$date_inside_temp[] = strtotime($row[0]) *1000;

then you add a datetime format in your code

$date_inside_temp[] = date_format($row[0]," Y-m-d H:i:s,");`. 

Then you try to convert that back into milliseconds by using Date.UTC() on the array of mixed dateobjects.

categories: Date.UTC(<?php echo $date_inside_temp;?>),