Reusing computed columns in mysql

How can I reuse a computed column in SQL in MySQL? Say: my query is something like: -

SELECT 
    CONVERT_TZ(
        if(timestamp_start > last_update, timestamp_start, last_update), 
        'GMT', 
        user.timezone
    ) as time_usr_tz
from 
    shecdule
    inner join user on shecdule.user_id = user.user_id
where 
    CONVERT_TZ(
        if(timestamp_start > last_update, timestamp_start, last_update), 
        'GMT', 
        user.timezone
    )
    < CURRENT_TIMESTAMP();

If you see the query the "CONVERT_TZ....." part is repeated.

This is only a sample query. Actually, I have to use that computed column several times. So if I make change in one place, I have to change in many places. And the size of the query becomes scary, too.

Is there any way to avoid such duplication?

UPDATE I already have sub-query in my original query, so sub-query is not a preferable option. Is it possible in any other way?

From stackoverflow

• SELECT  ctz
  FROM    (
          SELECT  shecdule.*,
                  CONVERT_TZ(
                  if(timestamp_start > last_update, timestamp_start, last_update), 
                  'GMT', 
                  user.timezone
                  ) AS ctz
          FROM    shecdule
          INNER JOIN
                  user
          ON      user.user_id = s.user_id
          ) s
  WHERE  ctz < CURRENT_TIMESTAMP()
  

  Note that this query doesn't use the indexes on timestamp_start and last_update efficiently.

  See the performance details in the article in my blog:

  • Selecting timestamps for a time zone

  In two words, you better use this:

  SELECT  ctz
  FROM    (
          SELECT  shecdule.*,
                  CONVERT_TZ(
                  if(timestamp_start > last_update, timestamp_start, last_update), 
                  'GMT', 
                  user.timezone
                  ) AS ctz
          FROM    shecdule
          INNER JOIN
                  user
          ON      user.user_id = s.user_id
          WHERE   timestamp_start < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
                  OR last_update < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
          ) s
  WHERE  ctz < CURRENT_TIMESTAMP()
  

  This inner query will perform coarse filtering on timestamp_start and last_update using indexes (no zone conversion can offset the time more than +14:00 hours from UTC).

  The outer subquery will then fine filter the results based on user's time zone.

  If you don't like subqueries, you may also use:

  SELECT  shecdule.*,
          CONVERT_TZ(
          if(timestamp_start > last_update, timestamp_start, last_update), 
          'GMT', 
          user.timezone
          ) AS ctz
  FROM    shecdule
  INNER JOIN
         user
  ON      user.user_id = s.user_id
  /* 
          WHERE   timestamp_start < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
                  OR last_update < CURRENT_TIMESTAMP() + INTERVAL 14 HOUR
  */
  HAVING  ctz < CURRENT_TIMESTAMP()