Advanced Report Usage Notes: Filter on Raw Values

Filtering on computed values in most cases prevents MySQL from being able to take advantage of indexes. For example, the following will force a scan of every record in TBL_TRANSFER_SESSIONS, because MySQL has to perform the CONVERT( ) on ts.started_at for every record:

SELECT DISTINCT ts.*
FROM
	$TBL_TRANSFER_SESSIONS ts
WHERE
	CONVERT(ts.started_at, DATE) = DATE(NOW())
;

Instead, compute the correct criteria to compare the raw value against:

SET @todays_date = DATE(NOW());
SET @tomorrows_date = DATE_ADD(@todays_date, INTERVAL 1 DAY);
SELECT DISTINCT ts.*
FROM
	$TBL_TRANSFER_SESSIONS ts
WHERE
	ts.started_at >= @todays_date
	AND ts.started_at < @tomorrows_date
;
Note: Even the above will only give expected results if you are in GMT time zone, as NOW( ) will return UTC time.

The builtin report variables $REPORT_PERIOD_START and $REPORT_PERIOD_END contain datetimes converted from local time zone of input into UTC and are usually a better choice for date filtering (unless recipient is fine with UTC-based filtering).