Get all records from current day based on datetime in different time zone

Question

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.

Answer 1

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');

Answer 2

If the current date/time is 2023-10-23 04:00:00 UTC and we want all records for current CST day, we need to convert current date/time to CST to get the current date in CST (2023-10-22), and then convert back to UTC:

WHERE gmtDateTime >= CONVERT_TZ(DATE(CONVERT_TZ(NOW(), '+00:00', '-06:00')), '-06:00', '+00:00')
  AND gmtDateTime <  CONVERT_TZ(DATE(CONVERT_TZ(NOW(), '+00:00', '-06:00')), '-06:00', '+00:00') + INTERVAL 1 DAY

For 2023-10-23 04:00:00 UTC this evaluates to:

WHERE gmtDateTime >= '2023-10-22 06:00:00'
  AND gmtDateTime <  '2023-10-23 06:00:00'

For 2023-10-23 12:39:27 UTC this evaluates to:

WHERE gmtDateTime >= '2023-10-23 06:00:00'
  AND gmtDateTime <  '2023-10-24 06:00:00'