CODEWITHSUNDEEP
mysqldatetime
Prateek
Prateek

Reputation: 1247

Subtract 1 month from datetime column from database

I need a query which subtracts 1 month from the date stored in the datetime so that i can use it in google line chart.My query now is

$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 ";

Subtracting 1 month is like "0 for Jan", "1 for Feb"....., "11 for Dec".

I have used dateadd & datesub but didn't get the desired result and end up with errors.

Update

Here is the complete code.

$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);

My Output : {"v":"Date(2017,01,02)","f":null},{"v":6,"f":null}

Desired Output : {"v":"Date(2017,00,02)","f":null},{"v":6,"f":null}

Upvotes: 0

Views: 845

Answers (3)

Prateek
Prateek

Reputation: 1247

Didn't found any proper solution in mysql. So I'm using php code to get the desired output. Here is the code used to subtract 1 month

$vDate = str_replace("-","",$row['visit_date_time'])-100;
$vDate = substr_replace($vDate,",",6,0);
$vDate = substr_replace($vDate,",",4,0);

If there is any solution in mysql . please let me know.

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use this in your query:

SELECT DATE_FORMAT('2017-01-21 12:36:32','%m') -1;

Result: 0

sample

to get get number of month starting with 0. month_number 1 and 2 are gives the same result

$sql_query = "SELECT count(ip_address) AS count, 
               DATE(visit_date_time) as visit_date_time,
               MONTH(visit_date_time) as month_number_1,
               DATE_FORMAT(visit_date_time,'%m') -1 as month_number_2
            FROM db_views 
            WHERE user_id = $id 
            GROUP BY DATE(visit_date_time) 
            ORDER BY visit_date_time LIMIT 30 ";

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

Use MONTH function to extract the month from the datetime (probably visit_date_time in your case) and subtract 1 from it.

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

Upvotes: 1

Related Questions