The answer lies in fully understanding the TIMEDIFF function and what it returns as an integer.
First, let's consider TIMEDIFF in its native element and what it returns:
mysql> select timediff('0:0:0','1:0:0') as timediff;
+-----------+
| timediff |
+-----------+
| -01:00:00 |
+-----------+
1 row in set (0.00 sec)
Second, what does TIMEDIFF return in an integer context - done by adding zero:
mysql> select timediff('0:0:0','1:0:0')+0 as timediff2;
+--------------+
| timediff2 |
+--------------+
| 10000.000000 |
+--------------+
1 row in set (0.01 sec)
Now, we use the TIME_TO_SEC function to return what we actually want:
mysql> select time_to_sec(timediff('0:0:0','1:0:0')+0)/60 as timediff3;
+-----------+
| timediff3 |
+-----------+
| 60.0000 |
+-----------+
1 row in set (0.00 sec)
The integer value returned by TIMEDIFF is not minutes or seconds, but rather a base-10 representation of the actual time - so 01:00:00 becomes 10000, and 05:05:01 becomes 50501.
Instead of using TIME_TO_SEC one can also use TIMESTAMPDIFF instead of TIMEDIFF and set the units to whatever is desired:
mysql> select timestampdiff(MINUTE,'2012-04-13 0:0:0','2012-04-13 1:0:0') as timediff4;
+-----------+
| timediff4 |
+-----------+
| 60 |
+-----------+
1 row in set (0.00 sec)
Instead of MINUTE, you can also use FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
The time functions in MySQL (in this case, version 5.1) are all described in the manual. The online manual also links to other versions (such as MySQL 5.5) as well.
Aaron's answer is essentially right, but I would not recommend DATEDIFF in the WHERE clause because it could possibly impose a table scan. Instead, I suggest this
SELECT
user.*,
company.*.
DATEDIFF(Company.CreatedOn,NOW()) NumDays
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
WHERE company.CreatedOn < DATE(NOW() - INTERVAL 1 YEAR);
You could take further and refactor the query as follows:
SELECT
U.*,
C.*.
DATEDIFF(C.CreatedOn,NOW()) NumDays
from
(select * from dbname.company
WHERE CreatedOn < DATE(NOW() - INTERVAL 1 YEAR)) C
inner join dbname.user U
on U.company_id = C.company_id;
Best Answer
In this case I would use a where clause to further qualify the data you are getting back in this case something like:
Since you said you are also trying to return only 4 rows you should use LIMIT 4 in your query, however I'm not sure if that would go before or after your ORDER BY.
If you have tried some things, let us know what you have already tried and what the errors are, this can help prevent people from reinventing the wheel and get your problem fixed faster.