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?

Maybe a [derived table](https://stackoverflow.com/questions/26094753/is-there-a-way-to-calculate-repeating-calculations-only-once-in-sql) can help? — Stefan M, Mar 21 '21 at 11:17
If you have UNIX timestamps in your database you're not helping yourself here. MySQL can index `DATETIME` columns and then it's easy to do a `BETWEEN` on some `DATE_SUB(...)` values. These have the advantage of being indexed. What you have here requires a very painful table scan. — tadman, Mar 21 '21 at 11:17
@KaziMohammadAliNur Good point, removed that. I think your solution is right on point. — tadman, Mar 21 '21 at 11:25

score 6 · Answer 1 · edited Jan 29 '23 at 13:27

6

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

edited Jan 29 '23 at 13:27

pdjota

3,163
2
23
33

answered Mar 21 '21 at 11:17

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

score 2 · Answer 2 · edited Mar 21 '21 at 11:31

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.

edited Mar 21 '21 at 11:31

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

answered Mar 21 '21 at 11:20

dani herrera

48,760
8
117
177

score 2 · Answer 3 · answered Mar 21 '21 at 11:38

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.

score 0 · Answer 4 · answered Mar 21 '21 at 11:33

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

How to perform SQL without duplicate calculation

4 Answers4