Hi,

I'm trying to get the difference between the current date and the date stored in the database. I have attempted to calculate the difference and made the php current date the same format as the date in the database and it doesn't work, can anyone see why?

PHP Code:

$date1 = date('Y-m-j'); // yyyy-mm-dd
$date2 = $actionId['dueDate'];
echo $date1;
echo $date2;
 
$calculateDiff = $date1 - $date2;
echo $calculateDiff;
$dateDiff = floor($dateDiff/(60*60*24));   echo " Differernce is $dateDiff days"; 


Thanks,

Well, the string representing the date is not a normal number, as there are 12 months in a year, and approximately 30 days in a year. Normal math does not apply. You will need to obtain a serial number (I believe a Unix timestamp will work), then convert the result of the difference to whatever date format you wish.

For the easy solution, on mysql, I would do it that way (!! works only if you want the years alone, months/days computing are messed due to leap years !!):
http://ma.tt/2003/12/calculate-age-in-mysql/

mysql> SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS('1985-02-08 21:28:45')), '%y')AS age;
+-------------------------------------+
| age                                 |
+-------------------------------------+
| 23 |
+-------------------------------------+
1 row in set (0.00 sec)

And if you can use postgresql, use it. It's better than mysql on so many points:

pgsql => select age(now(),'1985-02-08 21:28:45');
                   age
-----------------------------------------
 23 years 4 mons 17 days 18:17:54.229186
(1 row)

MySQL4 use

SELECT * FROM tables WHERE (TO_DAYS( NOW( ) ) - TO_DAYS( Stored_Date )>1)

for mySQL4.1+ you can use DATEDIFF(NOW(),Stored_Date)

Originally Posted by tripy

For the easy solution, on mysql, I would do it that way (!! works only if you want the years alone, months/days computing are messed due to leap years !!):
http://ma.tt/2003/12/calculate-age-in-mysql/
[/code]

Thanks for your comments everyone tripy, is there any way to put all that in one query?

hmm ?
I don't understand you...
What I wrote IS 1 query; but displayed with the resulting outpu in the mysql front-end.
There it is:

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS('1985-02-08 21:28:45')), '%y')AS age;

Obviously, you would change the
TO_DAYS('1985-02-08 21:28:45') part to a field who holds your date in your table

Sorry I just realised it was one query I managed to get the query to work and it retrieves some data but it doesn't retrieve the difference in days:

PHP Code:

SELECT * FROM action WHERE (TO_DAYS( NOW( ) ) - TO_DAYS( dueDate )>1) 


I want to get the difference in days between the dueDate stored in the action table and NOW() what does the > 1 do?

I then tried using this query which also worked but when I specify the table to get the date from there was an error so I probably put the SELECT * FROM table in the wrong place?

PHP Code:

SELECT * FROM action (FROM_DAYS(TO_DAYS(NOW())-TO_DAYS('dueDate')), '%d')AS DayDiff; 


Also, I want it to display the difference in days even if it isn't accurate because it will only be used for a rough estimate.

Thanks,

Anyone have any ideas?

sorry, I had missed your last post...
You don't want a select *, the day diff. IS waht you want, so put it into the select clause, not the from clause.
Try something like that:

SELECT (FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(action.dueDate)), '%d') AS DayDiff FROM action ;

I changed the query to the following and it seems to work

PHP Code:

SELECT (TO_DAYS( dueDate ) - TO_DAYS( NOW() ) ) AS dayDiff FROM action WHERE NOW() < dueDate 


It returns the field dayDiff and it appears to be calculating the difference in days from the current date and dueDate in the database. I'm trying to make a query that can work out how many days there are left until the due date so I put WHERE NOW() < dueDate

Thanks,