mysql - 从数据库的日期时间列中减去1个月

我需要一个从datetime中存储的日期中减去1个月的查询，以便可以在Google折线图中使用它。我的查询现在是

$sql_query = "SELECT count(ip_address) AS count,
                    DATE(visit_date_time) as visit_date_time
            FROM db_views
            WHERE user_id = $id
            GROUP BY DATE(visit_date_time)
            ORDER BY visit_date_time LIMIT 30 ";

减去1个月就像“ 0 for Jan”，“ 1 for Feb” .....，“ 11 for Dec”。

我使用了dateadd和datesub，但是没有得到想要的结果，并最终出现错误。

更新资料

这是完整的代码。

$sql_query = "SELECT count(ip_address) AS count, DATE(visit_date_time) as visit_date_time, MONTH(DATE(visit_date_time)) - 1 mon FROM db_views WHERE user_id = '".$_SESSION[$mainfunctions->GetLoginSessionVar()]."' GROUP BY DATE(visit_date_time) ORDER BY visit_date_time LIMIT 30 ";
$result = mysqli_query($mainfunctions->connection,$sql_query);

echo "{ \"cols\": [ {\"id\":\"\",\"label\":\"Date\",\"pattern\":\"\",\"type\":\"date\"}, {\"id\":\"\",\"label\":\"Views\",\"pattern\":\"\",\"type\":\"number\"} ], \"rows\": [ ";
$total_rows = mysqli_num_rows($result);
$row_num = 0;
while($row = mysqli_fetch_array($result)){
    $row_num++;
    $vDate = str_replace("-", ",",$row['visit_date_time']);
    if ($row_num == $total_rows){
      echo "{\"c\":[{\"v\":\"Date(".$vDate.")\",\"f\":null},{\"v\":" . $row['count'] . ",\"f\":null}]}";
    } else {
      echo "{\"c\":[{\"v\":\"Date(".$vDate.")\",\"f\":null},{\"v\":" . $row['count'] . ",\"f\":null}]}, ";
    }
}
echo " ] }";
mysqli_close($mainfunctions->connection);

我的输出：{"v":"Date(2017,01,02)","f":null},{"v":6,"f":null}

所需输出：{"v":"Date(2017,00,02)","f":null},{"v":6,"f":null}

最佳答案

使用MONTH函数从日期时间中提取月份（在您的情况下可能为visit_date_time），然后从中减去1。

SELECT
    COUNT(ip_address) AS count,
    DATE(visit_date_time) AS visit_date_time,
    MONTH(DATE(visit_date_time)) - 1 mon
FROM
    db_views
WHERE
    user_id = $id
GROUP BY DATE(visit_date_time)
ORDER BY visit_date_time
LIMIT 30

关于mysql - 从数据库的日期时间列中减去1个月，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41781729/