How to perform SQL without duplicate calculation

Question

I have following code:

SELECT *
FROM tier  
WHERE  
    TIMESTAMPDIFF( HOUR, now(), FROM_UNIXTIME(expireAt) )  < 72
AND TIMESTAMPDIFF( HOUR, now(), FROM_UNIXTIME(expireAt) )  > 0

You can see, that there is a TIMESTAMPDIFF calculation twice.

Is there any way how to perform this calculation only once?

Answer 1

You can use between. Since between includes range boundaries, instead of 0 and 72 you need to use 1 and 71.

SELECT *
FROM tier  
WHERE  
    TIMESTAMPDIFF( HOUR, now(), FROM_UNIXTIME(expireAt) ) between 1 and  71

Answer 2

CTE at rescue:

With CTE as
(
SELECT 
  *,
  TIMESTAMPDIFF( HOUR, now(), FROM_UNIXTIME(expireAt) ) as t
FROM tier  
)
Select *
From cte
Where t > 0 and t < 72

Be aware, this is not index friendly.

Answer 3

NOTE:

It is never a good idea to make a calcultion on a field expireAt and then compaire it with 1 or 2 constants. In this case MySQL must do this calculation on each ROW and this will bee a FULL TABLESCAN and can never be use a INDEX.

Its much better to calculate the range one time and compire this with the field like

SELECT *
FROM tier  
WHERE expireAt BETWEEN
    UNIX_TIMESTAMP(NOW() - INTERAVAL 71 HOUR))
AND
    UNIX_TIMESTAMP(NOW() - INTERAVAL 1 HOUR));

Answer 4

I suspect that you want everything that has expired in the past 72 hours:

SELECT t.*
FROM tier  
WHERE expire_at < UNIX_TIMESTAMP() and
      expire_at > UNIX_TIMESTAMP() - 72 * 60 * 60;

If you really want this aligned on the hours, you can still do the operations on the Unix timestamp values:

SELECT t.*
FROM tier  
WHERE expire_at < FLOOR(UNIX_TIMESTAMP() / (60 * 60)) * 60 * 60
      expire_at > FLOOR( (UNIX_TIMESTAMP() - 72 * 60 * 60) / (60 * 60))) * 60 * 60;

Some important notes:

• If you are storing date/time values as Unix timestamps, often there is no need to convert to date/datetime values for calculations.
• For performance reasons, you want to avoid functions on the column, these impede the optimizer.
• By contrast, expressions on "constants" (including the current date/time) should only be evaluated once by the query engine.