I have a google Line Chart which pulls data from a MySQL database. The very first column is in timestamp format, i.e. "1999-06-15 20:49:00" and is displayed along the horizontal axis of the Line Chart. The problem I have is that I can't get the above timestamp to display as just the year, i.e. "1999" on the horizontal axis instead of "1999-06-15 20:49:00".
I have the following line of code in my chart options hAxis: {title: 'Year', format: 'yyyy'}, which I assumed would give me the date formatted as "yyyy' on the horizontal axis. However the date still shows in the above format, i.e. yyyy-mm-dd hh:mm:ss along the horizontal axis instead of yyyy.
My Line Chart code is as follows:
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {packages: ['corechart']}).then(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
//['class Name','Highest Annual Observed Tide','Highest Astronomical Tide','Highest Annual Predicted Tide','Number of High Tides > HAT'],
['Data',{type: 'number', label: 'Highest Annual Observed Tide'}, {type: 'number', label: 'Highest Astronomical Tide'},{type: 'number', label: 'Highest Annual Predicted Tide'},{type: 'number', label: 'Number of High Tides >= HAT'}],
<?php
$con = mysqli_connect('localhost','sealevel_Temp','Stackoverflow','sealevel_NZ');
$query = "SELECT * FROM `Tides` WHERE Location = 'Auckland'";
$exec = mysqli_query($con,$query);
while($row = mysqli_fetch_array($exec)){
//echo "['".date("Y",strtotime($row['Timestamp']))."',".$row['Highest Annual Observed Tide'].",".$row['Highest Astronomical Tide'].",".$row['Highest Annual Predicted Tide'].",".$row['Observed High Tides > HAT']."],";
echo "['".$row['Timestamp']."',".$row['Highest Annual Observed Tide'].",".$row['Highest Astronomical Tide'].",".$row['Highest Annual Predicted Tide'].",".$row['Observed High Tides > HAT']."],";
}
?>
]);
var formatMSL = new google.visualization.NumberFormat({pattern: '# mm'});
// format MSL data into mm.
formatMSL.format(data, 1);
formatMSL.format(data, 2);
formatMSL.format(data, 3);
var options = {
fontSize: '20',
title: 'Auckland Extreme Tides Summary',
titleTextStyle: {fontSize: 20, fontName: 'Arial', color: 'Black', bold:'True'},
hAxis: {title: 'Year', format: 'yyyy'},
height: 600,
chartArea: {height: '75%', width: '80%', left: 100, right: 100},
legend: {position: 'top', alignment: 'start', textStyle: {fontSize: 8, fontName: 'Helvetica'}, maxLines: 5},
colors: ['blue'],
tooltip: {isHtml: true},
// Gives each series an axis name that matches the Y-axis below.
series: {
0: {targetAxisIndex: 0, color:'blue'},
1: {targetAxisIndex: 0, color:'gray'},
2: {targetAxisIndex: 0, color:'#0099C6'},
3: {targetAxisIndex: 1, color:'navy'},
},
vAxes: {
// Adds titles to each axis.
0: {title: 'Height above Chart Datum (mm)', viewWindow:{min: 3500, max: 4150}, format:'###0'},
1: {title: 'Number of Observed High Tides > HAT', viewWindow: {min: 0, max: 2}},
},
theme: 'material'
};
var chart = new google.visualization.LineChart(document.getElementById("AucklandChart_div"));
chart.draw(data,options);
}
</script><body>
<div id="AucklandChart_div"></div>
</body>the timestamp is being loaded into the data table as a string.
to load as a date, try changing this...
echo "['".$row['Timestamp']."',".$row['Highest Annual Observed Tide'].",".$row['Highest Astronomical Tide'].",".$row['Highest Annual Predicted Tide'].",".$row['Observed High Tides > HAT']."],";
to...
echo "[new Date('".$row['Timestamp']."'),".$row['Highest Annual Observed Tide'].",".$row['Highest Astronomical Tide'].",".$row['Highest Annual Predicted Tide'].",".$row['Observed High Tides > HAT']."],";