Google图表中的MySQL日期时间 [英] MySQL Datetime in Google Chart
问题描述
我正在研究MySQL的图表，它在线图中表现良好，但是当我更改为注解图时，由于它需要日期/时间，因此它给了我以下错误，我将类型更改为datetime（是字符串）并仍然有错误。
I'm working on a chart from MySQL, it worked fine as a linechart but when I changed to annotationchart it gave me the following error due to it needing a date/time, I changed the type to datetime (was string) and still have the error.
Type mismatch. Value 2014-07-23 19:03:16 does not match type datetime
原始代码
<?php
$con=mysql_connect("ip","user","pass") or die("Failed to connect with database!!!!");
mysql_select_db("db", $con);
$sth = mysql_query("SELECT * FROM db.table");
$data = array (
'cols' => array(
array('id' => 'date', 'label' => 'date', 'type' => 'datetime'),
array('id' => 'Temp', 'label' => 'Temp', 'type' => 'number'),
array('id' => 'Humid', 'label' => 'Humid', 'type' => 'number')
),
'rows' => array()
);
while ($res = mysql_fetch_assoc($sth))
// array nesting is complex owing to to google charts api
array_push($data['rows'], array('c' => array(
array('v' => $res['TIME']),
array('v' => $res['TEMP']),
array('v' => $res['HUMID'])
)));
?>
<html>
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1.1', {'packages':['annotationchart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var bar_chart_data = new google.visualization.DataTable(<?php echo json_encode($data); ?>);
var options = {
title: 'Weather Station'
};
var chart = new google.visualization.AnnotationChart(document.getElementById('chart_div'));
chart.draw(bar_chart_data, options);
}
</script>
</head>
<body>
<div id="chart_div" style="width: 900px; height: 500px;"></div>
</body>
</html>
推荐答案
datetime数据类型需要一个非常特定的语法用于数据输入。在使用JSON时，数据应该以这种格式构造为一个字符串：'Date（year，month，day，hours，minutes，seconds，milliseconds）'，其中全部 month 之后的选项是可选的（默认为 1 0 所有其他）和 month 是零索引的（所以一月份是 0 不是 1 ）。
The "datetime" data type requires a very specific syntax for data input. When using JSON, the data should be constructed as a string in this format: 'Date(year, month, day, hours, minutes, seconds, milliseconds)', where all options after month are optional (default is 1 for day and 0 for all others) and month is zero-indexed (so January is 0 not 1).
您可以像这样转换日期时间：
You can convert your date times like this:
while ($res = mysql_fetch_assoc($sth)) {
// assumes dates are patterned 'yyyy-MM-dd hh:mm:ss'
preg_match('/(\d{4})-(\d{2})-(\d{2})\s(\d{2}):(\d{2}):(\d{2})/', $res['TIME'], $match);
$year = (int) $match[1];
$month = (int) $match[2] - 1; // convert to zero-index to match javascript's dates
$day = (int) $match[3];
$hours = (int) $match[4];
$minutes = (int) $match[5];
$seconds = (int) $match[6];
array_push($data['rows'], array('c' => array(
array('v' => "Date($year, $month, $day, $hours, $minutes, $seconds)"),
array('v' => $res['TEMP']),
array('v' => $res['HUMID'])
)));
}
这篇关于Google图表中的MySQL日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
