I have a simple system to monitor and plot statistical data from a MySQL server. The services are written in PHP and return JSON. The issue am stuck with is that as today is July 1, 2014 ... values from the last seven days graph and table became zero. The date calculation logic is giving correct dates in the table output. I tried to manually call the service and check, and indeed the values in JSON are all zero. Most of the queries in this module utilise the following:
$query_string = SELECT new_user_count AS new_user_count
FROM kpi_summaries
WHERE DATE(date) = CURDATE()-".$n."
AND data_type=1";
In a simple loop, I am using this query string to get values for 7 last days from every day. This worked perfect till yesterday (since I guess, dates after subtraction from CURDATE were all in the same month).
Another way to phrase my doubt is: Is it not correct to use CURDATE()-3 if 3 days ago was a date in the last month ? If this is wrong, what is the correct way to go about it ? Will I need to use DATE_SUB() or something like that ..?
Here is the problem. CURDATE() -- despite appearances and logic -- doesn't return a date value. It returns a string or number. To quote the documentation:
Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
What is happening is that the context expects a number for -. So, 20140701 - 3 is . . . 20140698. That isn't sensible as a date. It doesn't match anything.
By adding the interval we give the compiler a hint that CURDATE() should really be a date and to subtract the appropriate number of days. Of course, using a function such as date_sub() would have the same effect.