Let’s suppose as a result of a LEFT JOIN
I end up with a Films
table for example:
id | title | begins | ends |
---|---|---|---|
1 | The Lord Of The Rings | 2024-04-25 17:46:00 | 2024-04-25 20:46:00 |
2 | The Matrix | 2024-06-05 11:46:00 | 2024-06-05 13:46:00 |
Where both begins
and ends
are of the type timestamp without timezone
.
I want to get records with the following priority:
- Films that are currently running
- Films that will run from now until specified
endTime
- Films that have ran in the past 3 days
I can do something like:
SELECT * from FILMS WHERE begins >= Now()::timestamp - INTERVAL '3 days' AND ends < endTime::timestamp
ORDER BY
CASE
WHEN endTime::timestamp > Now()::timestamp AND starts < Now()::timestamp THEN 1
WHEN starts < Now()::timestamp THEN 2
ELSE 3
END;
LIMIT lim;
Is there a way to use indexes to make this faster?