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.
The easiest way is to have a calendar table, defined in the following way:
CREATE TABLE calendar
(
a_day date PRIMARY KEY
) ;
... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:
INSERT INTO
calendar (a_day)
VALUES
('2013-01-01'),
('2013-01-02'),
('2013-01-03'),
('2013-01-04'),
('2013-01-05'),
-- everything up to
('2013-12-31') ;
At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:
SELECT
t.id, c.a_day
FROM
t
JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date
ORDER BY
t.id, c.a_day ;
... and get
id | a_day
-: | :---------
1 | 2013-01-14
1 | 2013-01-15
1 | 2013-01-16
1 | 2013-01-17
1 | 2013-01-18
2 | 2013-02-01
2 | 2013-02-02
2 | 2013-02-03
2 | 2013-02-04
You can see all the setup at dbfiddle here
Best Answer
If you need all items (ever the data on @first_date or @second_date is not present), rewrite from cartesian to FULL JOIN and use COALESCE to replace NULLs with zeros.