CODEWITHSUNDEEP
phpmysqldatetime
IntenseMX
IntenseMX

Reputation: 61

sql timediff function

What is the proper way to use mysql TIMEDIFF, (or something similar)? In database I have a column called date, and it is setup CURRENT_TIMESTAMP. I'm trying to calculate the differene between the CURRENT_TIMESTAMP and right now in a row, and than echo that to all the rows.. here's my script

  <?php
  mysql_connect("localhost","root","");//database connection
  mysql_select_db("beyondmotors");

  $result = mysql_query("SELECT * FROM currentrentals");

  echo "<table border='2'>
  <tr>
  <th>ID</th>
  <th>Year</th>
  <th>Make</th>
  <th>Model</th>
  <th>First Name</th>
  <th>Last Name</th>
  <th>Insurance</th>
  <th>Date</th>
  <th>Notes</th>
  </tr>";

  while($row = mysql_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
    echo "<td>" . $row['year'] . "</td>";
    echo "<td>" . $row['make'] . "</td>";
    echo "<td>" . $row['model'] . "</td>";
    echo "<td>" . $row['firstname'] . "</td>";
    echo "<td>" . $row['lastname'] . "</td>";
    echo "<td>" . $row['insurance'] . "</td>";
    echo "<td>" . $row['date'] . "</td>";
    echo "<td>" . $row['notes'] . "</td>";
      echo ("<td><a href=\"edit_form.php?id=$row[id]\">Edit</a></td></tr>");
      echo "</tr>";
      }
    echo "</table>";

Thanks in advance

Upvotes: 1

Views: 981

Answers (2)

Jason McCreary
Jason McCreary

Reputation: 73031

This can be heavily dependent on your output.

For example, TIMEDIFF() outputs is hours, minutes, seconds. As such, it is limited to the data type TIME.

Given a unit of SECOND, TIMESTAMPDIFF() is a more robust solution.

All of this is well documented in MySQL Date and Time functions.

In the end, you typically see these calculations done with code. That is not with SQL. In your case PHP. You could do simple arithmetic by subtracting the timestamps from strtotime(). Or if you are using PHP >= 5.3, you could use DateTime::diff.

As an aside, avoid naming your columns keywords, i.e. date.

Upvotes: 3

F. M&#252;ller
F. M&#252;ller

Reputation: 4062

I would do like this:

SELECT (fields), UNIX_TIMESTAMP(date) as date FROM currentrentals

And then you can access date with $row['date']. Now you can easily subtract these dates.

$timesince = date(format, time() - $row['date']);

Hope that helps.

Upvotes: 0

Related Questions