Version 5.x of MySQL have added a strict checking of dates. When using Reports and selecting a monthly report that has only 30 days (or 28/29 for February) will return No data found !!'''.
The select string looks like this for February:
SELECT substring(calldate,1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE dst='2400' AND UNIX_TIMESTAMP(calldate) >= UNIX_TIMESTAMP('2008-02-01') AND UNIX_TIMESTAMP(calldate) <= UNIX_TIMESTAMP('2008-02-31 23:59:59') GROUP BY substring(calldate,1,10);
Note that the ending date is set to '2008-02-31 23:59:59' and that is an invalid date.
In my CDR database I have a lot of entries and the result for the above select is:
Empty set, 4879 warnings (0.38 sec)
To see the warnings:
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '2008-02-31 23:59:59' |
| Warning | 1292 | Truncated incorrect datetime value: '2008-02-31 23:59:59' |
| Warning | 1292 | Truncated incorrect datetime value: '2008-02-31 23:59:59' |
The following set command allows invalid dates in the current session:
mysql> set sql_mode='allow_invalid_dates';
Query OK, 0 rows affected (0.00 sec)
Or you can set it as a global:
set global sql_mode='allow_invalid_dates';
I will set this ticket to Milestone 3.0. If this should go in 2.5 a check could be implemented in the install script so that the global setting will be set if MySQL is 5.x or later.
