[SOLVED] php and mysql timestamp

[whitedragon101]

I have been trying to find a complete example of a php page running a mysql database calculating the difference between 2 timestamp fields in days.  I have concocted a bit of code but it still does not work.  Any help would be appreciated

 

from_date and to_date are both TIMESTAMP's

 

mysql_select_db($database_myconnect, $myconnect);

$query_job_req_set = sprintf("SELECT * FROM job_request WHERE job_id = %s", GetSQLValueString($colname_job_req_set, "int"));

$job_req_set = mysql_query($query_job_req_set, $myconnect) or die(mysql_error());

$row_job_req_set = mysql_fetch_assoc($job_req_set);

$totalRows_job_req_set = mysql_num_rows($job_req_set);

 

 

$date1 = $row_job_req_set['bricklayer_from_date'];

$date2 = $row_job_req_set['bricklayer_to_date'];   

$queryDateDiff = sprintf("SELECT TO_DAYS($date1) - TO_DAYS($date2) FROM job_request WHERE job_id=%s", GetSQLValueString($colname_job_req_set, "int"));

$date_req_set = mysql_query($queryDateDiff, $myconnect) or die(mysql_error());

$totalRows_date_req_set = mysql_num_rows($date_req_set);

 

echo "number of rows $totalRows_date_req_set";

$date_row = mysql_fetch_row($date_req_set);

     

echo " The result is $date_row[0]"; 

[PFMaBiSmAd]

Are the values Unix TIMESTAMPs or are they Mysql TIMESTAMPs?

 

And datediff() will do what you want -

 

DATEDIFF(expr1,expr2)

 

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

 

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');

        -> 1

mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');

        -> -31

 

And why not just use a single query? There is no point in executing a query to get two values, then put them back into another query just to use a mysql function to calculate the difference. Put the mysql function(s) into the first query.

[whitedragon101]

Are the values Unix TIMESTAMPs or are they Mysql TIMESTAMPs?

 

And datediff() will do what you want -

 

DATEDIFF(expr1,expr2)

 

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

 

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');

        -> 1

mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');

        -> -31

 

And why not just use a single query? There is no point in executing a query to get two values, then put them back into another query just to use a mysql function to calculate the difference. Put the mysql function(s) into the first query.

 

They are mysql time stamp values

 

so am I correct that this is what you mean if written in php:

 

$date1 = $row_job_req_set['bricklayer_from_date'];

$date2 = $row_job_req_set['bricklayer_to_date'];

$difference = mysql_query("SELECT DATEDIFF($date2,$date1)");

echo "the difference is $difference";

 

would output the following

 

the difference is 4  (or whatever number it is for given dates)

 

 

 

[whitedragon101]

Have tried the above and not sure if it worked as I can't get at the result.  The result is displaying as

 

Resource id #5

 

However if I:

 

$differenceRow= mysql_fetch_array($difference);

echo "the difference is $differenceRow[0]";//also tried 1

 

Then the output is:

 

Array

 

 

[fenway]

Maybe you should check the php refman to see what mysql_fetch_array returns.

[whitedragon101]

 

Fixed it .  As always was nothing complex just a stupid syntax error staring me in the face.

 

PFMaBiSmAd's code at the command line was:

 

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');

 

so my php was

 

$difference = mysql_query("SELECT DATEDIFF($date2,$date1)");

 

and it turns out all i am missing was '

 

so this works just fine. and all the different ways I was trying to get at the variable all work

 

$difference = mysql_query("SELECT DATEDIFF('$date2','$date1')");