title: ChartJS - format datetime X axis when displaying data from MySql table
body: <p>I've adapted the example on this <a href="https://www.dyclassroom.com/chartjs/chartjs-how-to-draw-line-graph-using-data-from-mysql-table-and-php" rel="nofollow noreferrer">site</a> and have managed to get to a position where I can retrieve data from my MySql database and plot it ok, but I can't format the X axis as a date time axis correctly.</p>
  
  <p>linegraph.html:</p>
  
  <pre><code>&lt;!DOCTYPE html&gt;
  &lt;html&gt;
    &lt;head&gt;
      &lt;title&gt;ChartJS - LineGraph&lt;/title&gt;
      &lt;style&gt;
        .chart-container {
          width: 1000px;
          height: auto;
        }
      &lt;/style&gt;
    &lt;/head&gt;
    &lt;body&gt;
  tst1
      &lt;div class="chart-container"&gt;
        &lt;canvas id="mycanvas"&gt;&lt;/canvas&gt;
      &lt;/div&gt;
      tst2
      &lt;!-- javascript --&gt;
      &lt;script type="text/javascript" src="js/jquery.min.js"&gt;&lt;/script&gt;
      &lt;script type="text/javascript" src="js/Chart.js"&gt;&lt;/script&gt;
      &lt;script type="text/javascript" src="js/linegraph.js"&gt;&lt;/script&gt;
    &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>followersdata.php</p>
  
  <pre><code>&lt;?php
  //setting header to json
  header('Content-Type: application/json');
  
  //database
  define('DB_HOST', 'localhost');
  define('DB_USERNAME', 'xxxxxxxxxxxxxxxxxxxxx');
  define('DB_PASSWORD', 'xxxxxxxxxxxxxxxxxxxxx');
  define('DB_NAME', 'test');
  
  //get connection
  $mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
  
  if(!$mysqli){
    die("Connection failed: " . $mysqli-&gt;error);
  }
  
  //query to get data from the table
  $query = sprintf("SELECT reading_time, value1 FROM Sensor");
  
  //execute query
  $result = $mysqli-&gt;query($query);
  
  //loop through the returned data
  $data = array();
  foreach ($result as $row) {
    $data[] = $row;
  }
  
  //free memory associated with result
  $result-&gt;close();
  
  //close connection
  $mysqli-&gt;close();
  
  //now print the data
  print json_encode($data);
  </code></pre>
  
  <p>Example output of followersdata.php</p>
  
  <pre><code>[{"reading_time":"2020-02-24 22:38:48","value1":"1.10"},{"reading_time":"2020-02-24 22:39:18","value1":"1.10"},{"reading_time":"2020-02-24 22:39:49","value1":"1.10"},{"reading_time":"2020-02-24 22:40:19","value1":"1.10"},{"reading_time":"2020-02-24 22:40:49","value1":"1.10"},{"reading_time":"2020-02-24 22:41:19","value1":"1.10"},{"reading_time":"2020-02-24 22:41:49","value1":"1.10"},{"reading_time":"2020-02-24 22:42:19","value1":"1.10"},{"reading_time":"2020-02-24 22:42:49","value1":"1.10"},{"reading_time":"2020-02-24 22:43:19","value1":"1.10"},{"reading_time":"2020-02-24 22:43:50","value1":"1.10"},{"reading_time":"2020-02-24 22:44:20","value1":"1.10"},{"reading_time":"2020-02-24 22:44:50","value1":"1.10"},{"reading_time":"2020-02-24 22:45:20","value1":"1.10"},{"reading_time":"2020-02-24 22:45:50","value1":"1.10"},{"reading_time":"2020-02-24 22:46:21","value1":"1.10"},{"reading_time":"2020-02-24 22:46:51","value1":"1.10"},{"reading_time":"2020-02-24 22:47:21","value1":"1.10"},
  </code></pre>
  
  <p>linegraph.js:</p>
  
  <pre><code>$(document).ready(function(){
  $.ajax({
  url : "followersdata.php",
  
  type : "GET",
  success : function(data){
    console.log(data);
  
    var datetime = [];
    var value1  = [];
    //var twitter_follower = [];
    //var googleplus_follower = [];
  
    for(var i in data) {
      datetime.push(data[i].reading_time);
      value1.push(data[i].value1);
      //twitter_follower.push(data[i].twitter);
      //googleplus_follower.push(data[i].googleplus);
    }
  
    var chartdata = {
      labels: datetime,
      datasets: [
        {
          label: "Value1",
          fill: false,
          lineTension: 0.1,
          backgroundColor: "rgba(59, 89, 152, 0.75)",
          borderColor: "rgba(59, 89, 152, 1)",
          pointHoverBackgroundColor: "rgba(59, 89, 152, 1)",
          pointHoverBorderColor: "rgba(59, 89, 152, 1)",
          data: value1
        //},
        //{
        //  label: "twitter",
        //  fill: false,
        //  lineTension: 0.1,
        //  backgroundColor: "rgba(29, 202, 255, 0.75)",
        //  borderColor: "rgba(29, 202, 255, 1)",
        //  pointHoverBackgroundColor: "rgba(29, 202, 255, 1)",
        //  pointHoverBorderColor: "rgba(29, 202, 255, 1)",
        //  data: twitter_follower
        //},
        //{
        //  label: "googleplus",
         // fill: false,
         // lineTension: 0.1,
         // backgroundColor: "rgba(211, 72, 54, 0.75)",
         // borderColor: "rgba(211, 72, 54, 1)",
         // pointHoverBackgroundColor: "rgba(211, 72, 54, 1)",
         // pointHoverBorderColor: "rgba(211, 72, 54, 1)",
         // data: googleplus_follower
        }
      ],
      options: {
          maintainAspectRatio: false,
      animation: false,
          legend: {display: true  },
      scales:{xAxes: [{type: 'time',time: {unit: 'day',displayFormats: { 'day': 'MMM D' }}}]}
          }
  
  
  
   };
  
  
  
    var ctx = $("#mycanvas");
  
    var LineGraph = new Chart(ctx, {
      type: 'line',
      data: chartdata
    });
  },
  error : function(data) {
  
  }
  });
  });
  </code></pre>
  
  <p>Resulting chart:
  <a href="https://i.stack.imgur.com/PLvfU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PLvfU.png" alt="Graph output"></a></p>
  
  <p>I have tried using the options </p>
  
  <pre><code>options: {
      maintainAspectRatio: false,
  animation: false,
      legend: {display: true  },
  scales:{xAxes: [{type: 'time',time: {unit: 'day',displayFormats: { 'day': 'MMM D' }}}]}
      }
  </code></pre>
  
  <p>But it doesn't seem to format the axis as MMM D, or if I try hours, as hours etc.</p>
  
  <p>Where am I going wrong? How can I get the Options to work as the legend doesn't display either.</p>