ChartJS - format datetime X axis when displaying data from MySql table

Question

I've adapted the example on this site 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.

linegraph.html:

<!DOCTYPE html>
<html>
  <head>
    <title>ChartJS - LineGraph</title>
    <style>
      .chart-container {
        width: 1000px;
        height: auto;
      }
    </style>
  </head>
  <body>
tst1
    <div class="chart-container">
      <canvas id="mycanvas"></canvas>
    </div>
    tst2
    <!-- javascript -->
    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="js/Chart.js"></script>
    <script type="text/javascript" src="js/linegraph.js"></script>
  </body>
</html>

followersdata.php

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

//query to get data from the table
$query = sprintf("SELECT reading_time, value1 FROM Sensor");

//execute query
$result = $mysqli->query($query);

//loop through the returned data
$data = array();
foreach ($result as $row) {
  $data[] = $row;
}

//free memory associated with result
$result->close();

//close connection
$mysqli->close();

//now print the data
print json_encode($data);

Example output of followersdata.php

[{"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"},

linegraph.js:

$(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) {

}
});
});

Resulting chart:

I have tried using the options

options: {
    maintainAspectRatio: false,
animation: false,
    legend: {display: true  },
scales:{xAxes: [{type: 'time',time: {unit: 'day',displayFormats: { 'day': 'MMM D' }}}]}
    }

But it doesn't seem to format the axis as MMM D, or if I try hours, as hours etc.

Where am I going wrong? How can I get the Options to work as the legend doesn't display either.

You'll need to parse the dates as per your need either in php or at front-end before passing them as options args — Vedant Terkar
– Vedant Terkar, Commented Feb 28, 2020 at 22:57
How would I go about doing that? I have just tried datetime.push(Date.parse(data[i].reading_time)); in linegraph.js, and I can see the milliseconds in the graph now instead, so still no impact from the Options. Interestingly I have seen other examples where the input data is manually typed into a variable in the YYYY-MM-DD HH-MM-SS format without parse and it works. — JamesH
– JamesH, Commented Feb 28, 2020 at 23:14

uminder · Accepted Answer · 2020-02-29 11:33:29Z

1

The problem in your code is that the chart options is nested inside the data object and actually ignored by Chart.js. Move it up to the next hierarchically level. Otherwise the xAxis definition looks just fine. There's also no need for explicitly parsing the date as one comment suggests since it is in a format that can be parsed by Date.

Alternatively to your xAxis definition, you can do it the following simpler way, the default display format of unit: 'day' is actually what you're looking for ('MMM D').

xAxes: [{
  type: 'time',
  time: {
    unit: 'day'
  }
}]

See below simplified example:

const data = [
  {"reading_time":"2020-02-10 22:38:48","value1":"1.10"},
  {"reading_time":"2020-02-11 22:39:18","value1":"1.20"},
  {"reading_time":"2020-02-12 22:39:49","value1":"1.40"},
  {"reading_time":"2020-02-13 22:40:19","value1":"1.10"},
  {"reading_time":"2020-02-14 22:40:49","value1":"1.20"},
  {"reading_time":"2020-02-15 22:41:19","value1":"1.30"}
];

new Chart("mycanvas", {
  type: 'line',
  data: {
    labels: data.map(o => o.reading_time ),
    datasets: [{
      label: "Value1",
      fill: false,
      borderColor: "rgba(59, 89, 152, 1)",
      data: data.map(o => o.value1)
    }],
  },
  options: {
    scales: {
      xAxes: [{
        type: 'time',
        time: {
          unit: 'day'
        }
      }]
    }
  }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.3/Chart.bundle.js"></script> 
<canvas id="mycanvas" height="90"></canvas>

edited Feb 29, 2020 at 11:33

answered Feb 29, 2020 at 9:57

uminder

26.4k6 gold badges45 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JamesH Over a year ago

Thank you so much, this was very useful an allowed me to get what I wanted - much appreciated! One other thing I needed to do to get it working though (in case it is of use to anyone else), I needed to include <script type="text/javascript" src="moment.js"></script> before <script type="text/javascript" src="Chart.js"></script>.

Collectives™ on Stack Overflow

ChartJS - format datetime X axis when displaying data from MySql table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related