I need to select all records for the current day in CST, however the timestamp fields in the table are all in GMT/UTC.
The server's timezone is GMT.
Getting all records for the current day in GMT is very fast about 0.031 seconds fetch according to MySQL workbench, and for the view with the -6 added it takes about 40 seconds.
I've tried a few
CAST(CONVERT_TZ(`CallLog`.`gmtDateTime`, '+00:00', '-06:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-06:00') AS DATE)
gmtDateTime is a Timestamp, so might be able to do something with that?
I believe the reason it is slow is the left hand expression is calculated for every record in the table and can't be cached (However this is a guess, not super familiar with the internals of MySQL)
The obvious solution would be to add the CST time as a column and filter based on that, but unfortunately that isn't an option as we don't have any control over the software that populates the table, and would rather not modify the schema of the table itself.
It is most likely as you pointed out it has to convert your gmtDateTime for each row and do a full table scan.
Remove the conversion of gmtDateTime and select using a upper and lower bound timestamp based on NOW(). Your WHERE condition would then look something like this:
gmtDateTime BETWEEN
CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(NOW(), '+00:00', '-06:00'), '%Y-%m-%d 00:00:00'), '-06:00', '+00:00') AND
CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(NOW(), '+00:00', '-06:00'), '%Y-%m-%d 23:59:59'), '-06:00', '+00:00');