如何查看比SQL完成时间超过2天收到的报告

Question

Im trying to do a query to show only reports which were received more than two days after their end completion date, within a specified date range. I have this so far but cant quite figure out how to get the 'more than 2 days' part right. can anyone help me please.

    $result = mysqli_query($con, "SELECT * 
FROM tcards.vtmastertrail 
INNER JOIN tcards.vtcards ON tcards.vtcards.id = tcards.vtmastertrail.card_id
WHERE tcards.vtcards.colour = 'Beige' AND datetime_report_received > (inspdate_end + 2) AND
 inspdate_end >= '2014-04-01' AND inspdate_end <= '2014-04-30'
ORDER BY tcards.vtmastertrail.inspdate_end, datetime_report_received");

current output is a table like so:

        echo "<table border='1'>
<tr>
<th>Report End Date date</th>
<th>Received Report date</th>
<th>Job Finish Date</th>
<th>Client</th>
<th>Client Code</th>
<th>Employee</th>
</tr>";


while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['inspdate_end'] . "</td>";
  echo "<td>" . $row['datetime_report_received'] . "</td>";
  echo "<td>" . $row['datetime_completed_report'] . "</td>";
  echo "<td>" . $row['title'] . "</td>";
  echo "<td>" . $row['client_code'] . "</td>";
  echo "<td>" . $row['employee'] . "</td>";
  echo "</tr>";
}

echo "</table>";

This info is being pulled by the query and displayed in my table but all the records 'datetime_report_received' is not 2 days more than the 'inspdate_end'. It is just displaying all records which have a 'inspdate_end within the daterange ive specified. Same result as i get if i remove the + 2) part of the query. so the +2) part must'nt be doing anything

cant post pic as i dont have good enough rep.

Answer 1

If you used the PHP function substr() to get the last two characters of the date in the database (so substr($variable, -2)) then you could add 2 to that as you have done in your code to check whether the date was before or after today.

It might be worth storing the time as well because with this currently, if I had a record in the database submitted at 2014-07-27 23:59 and I checked it at 2014-07-29 00:01, it would still return that two days have passed from the original submission of the entry to the database, when in reality only 1 day and 2 minutes have passed.