Jummy01234 Jummy01234 - 24 days ago 11
SQL Question

ADD 30 day interval inside MySQL query

I currently have a working code that adds an order inside an event calendar, but I wan't to add the expiration date that comes with the order (30 days after the order has send).

//Include db configuration file
include 'connect.php';
$currentDate = date("Y-m-d H:i:s");
//Insert the event data into database
$insert = $conn->query("INSERT INTO events (title,date,created,modified,tht) VALUES ('".$title."','".$date."','".$currentDate."','".$currentDate."','".$date." + INTERVAL 30 DAY')");
if($insert){
echo 'ok';
}else{
echo 'err';
}


Currently this code is working just fine, however shipping date is the same as expiration date, instead of the 30 days later that I want. What am I missing here?

Answer

You can either do it in the php:

//Include db configuration file
include 'connect.php';
$currentDate = date("Y-m-d H:i:s");
$endDate = date("Y-m-d H:i:s", strtotime('+30 days', strtotime($date)));
//Insert the event data into database
$insert = $conn->query("INSERT INTO events (title,date,created,modified,tht) VALUES ('".$title."','".$date."','".$currentDate."','".$currentDate."','".$endDate."')");
if($insert){
    echo 'ok';
}else{
    echo 'err';
}

or in mysql directly:

//Include db configuration file
include 'connect.php';
$currentDate = date("Y-m-d H:i:s");
//Insert the event data into database
$insert = $conn->query("INSERT INTO events (title,date,created,modified,tht) VALUES ('".$title."','".$date."','".$currentDate."','".$currentDate."',DATE_ADD('".$date."', INTERVAL 30 DAY))");
if($insert){
    echo 'ok';
}else{
    echo 'err';
}

I would go for the first approach because at least the logic for calculation of the date will be only in one place - in php. Otherwise if you have difference in timezones set on your mysql and php servers you may experience difference in datetimes returned from php and mysql. So it is better to leave the datetime logic to be in php.

Comments