Difference between actual date and past date

supadema · #1 (**permalink**) 09-29-10, 10:58

Hello everybody,

I'm writing in this forum to ask some advice about do a difference between two dates.
I've seen a lot of material about this online, but I've not found what could be the better solution for me...

What I would like to get is the difference between the actual date-time and a past date-time... so something like "5 minutes ago", "1 hour ago", "1 day ago", etc.

In my table I've at the moment 5 columns: id, cat, rif, comment, date (datetime).
In the 'date' fields I have recorded the time when the comment has been sent.

Now, before to become too much crazy I was asking me what could be the better way to proceed...
For example I've seen the DATEDIFF function, but how do I use it in my case?

Thanks a lot for your time

healdem · #2 (**permalink**) 09-29-10, 11:39

handle it in your front end

if you do want to do it on the server you will ave to return the number of units (timediff looks promising) bear in mind datedff returns the difference between two dates.

it-iss.com · #3 (**permalink**) 09-29-10, 13:11

Hi,

a better solution would be to use UNIX_TIMESTAMP function to convert the date time value into a value representing the number of seconds since UNIX epoch seconds since '1970-01-01 00:00:00' UTC. Comparing the difference between these returns the number of seconds which can be converted to whatever you want.

supadema · #4 (**permalink**) 09-30-10, 04:41

I was doing some trial with the UNIX_TIMESTAMP, but I don't understand how to do... this the code I put down but I get a syntax error: "... check the manual that corresponds to your MySQL server version for the right syntax to use near '10:40:16) - UNIX_TIMESTAMP(2010-09-29 18:38:16)' at line 1"

$date = date('Y-m-d H:i:s',time());
$selData = "SELECT dataComm FROM comments";
$queryData = @mysql_query("$selData",$link) or die("Errore query database: " . mysql_error());
while ($rowData = mysql_fetch_array($queryData, MYSQL_NUM)) {
$diffTime = "SELECT UNIX_TIMESTAMP($date) - UNIX_TIMESTAMP($rowData[0]) ";
@mysql_query("$diffTime",$link) or die("Errore query database: " . mysql_error());
echo $diffTime;
}

healdem · #5 (**permalink**) 09-30-10, 07:44

so is this now a MySQL problem or a PHP problem?

supadema · #6 (**permalink**) 09-30-10, 08:25

It is a MySQL problem: Errore query database: You have an error in your SQL syntax;

r937 · #7 (**permalink**) 09-30-10, 08:53

Quote:

Originally Posted by supadema

In my table I've at the moment 5 columns: id, cat, rif, comment, date (datetime).

Code:

SELECT UNIX_TIMESTAMP -
       UNIX_TIMESTAMP(daTable.date) AS difference_in_seconds
  FROM daTable

and now it's a php problem

supadema · #8 (**permalink**) 10-01-10, 07:04

Unluckly is still a MySQL problem...

I've tried your code but I get this error:
Errore query database: Unknown column 'UNIX_TIMESTAMP' in 'field list'

So I've tried to add a column called dateNow (type: TIMESTAMP, default: CURRENT_TIMESTAMP) in my database and change the code like this:

$diffTime = "SELECT UNIX_TIMESTAMP(comments.dateNow) - UNIX_TIMESTAMP(comments.dataComm) AS difference_in_seconds FROM comments;";

$diffSec = $diffTime['difference_in_seconds'];
echo $diffSec;

Like this I don't get any error, but neither I get my result...
"echo $diffSec;" returns this: "SSSSSS"

it-iss.com · #9 (**permalink**) 10-01-10, 07:53

Hi,

here are a few ways in which this should be called. The last entry reproduces your error. Make sure that this is being called correctly.

Code:

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1285933281 | 
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp() - unix_timestamp('2007-11-30 00:00:00');
+----------------------------------------------------------+
| unix_timestamp() - unix_timestamp('2007-11-30 00:00:00') |
+----------------------------------------------------------+
|                                                 89556115 | 
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp - unix_timestamp('2007-11-30 00:00:00');
ERROR 1054 (42S22): Unknown column 'unix_timestamp' in 'field list'

supadema · #10 (**permalink**) 10-01-10, 09:16

That's right, with "unix_timestamp()" instead of "unix_timestamp" I don't get anymore that error... but I don't understand what is wrong, because I always get "S" as result!!