past records from current day
Just so I'm sure I understand, you are trying to get records from the past 5 years (so between "today - 5 years" and "today")?
And, when you say "current day", do you mean "relative to time now", or "same day"? The query example you gave
Code:
(CONVERT_TZ(app_tasks.due_date, "+0:00", "+00:00") >= CURDATE() - INTERVAL 5 YEAR AND CONVERT_TZ(app_tasks.due_date, "+0:00", "+00:00") < CURDATE())
... would include the time, so say the current time is 3pm, it'll show you due dates <= 3pm today.
Also, in that query, you aren't doing any TZ conversion, as you are specifying 0 from both zones.
If you don't want to include the time part, so just want to show tasks due "today" (so the date is today, ignoring the time part), use the DATE() function to extract just the date part of the due_date, so the WHERE clause would be (removing the TZ conversion for now):
Code:
(
DATE(app_tasks.due_date) >= CURDATE() - INTERVAL 5 YEAR
AND
DATE(app_tasks.due_date) <= CURDATE()
)
... or use the BETWEEN operator ...
Code:
DATE(app_tasks.due_date) BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()
In order to do a prefilter which can't be built with the GUI (which this can't, if you are needing to extract just the DATE(), you have to do a subquery, like this:
Field: id
Condition: IN
Value: SELECT id FROM app_tasks WHERE DATE(app_tasks.due_date) BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()
Type: query
So the "value" becomes a subquery that returns a result set of app_task ids that match the WHERE.
If you do need to do the TZ conversion, add it like this:
Value: SELECT id FROM app_tasks WHERE DATE(CONVERT_TZ(due_date, "+0:00", "+5:00")) BETWEEN CURDATE() - INTERVAL 5 YEAR AND CURDATE()
... which would add 5 hours to the datetime before extracting the date part. Which direction you need to modify your dates in (if at all) depends on whether you are storing the date in local of GMT, and what time zone your MySQL thinks it is in. Trial and error should tell you.
-- hugh