How to use CONVERT_TZ and AS?

Question

MySQL

I want to compare the converted date with an interval.

SELECT event, CONVERT_TZ(time, '+00:00', '-04:00') AS time1 FROM events 
WHERE time1 between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59' AND lang = 'ru'

Answer 1

An output field alias cannot be used in WHERE.
That's right - use a copy of the calculation value of the expression:

SELECT event, 
       CONVERT_TZ(time, '+00:00', '-04:00') AS time1 
FROM events 
WHERE CONVERT_TZ(time, '+00:00', '-04:00') between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59' 
  AND lang = 'ru'

The MySQL extension also allows the output field alias to be HAVING (provided the expression does not use window functions):

SELECT event, 
       CONVERT_TZ(time, '+00:00', '-04:00') AS time1 
FROM events 
WHERE lang = 'ru'
HAVING time1 between '2021-06-24 00:00:00' AND '2021-06-24 23:59:59'