I have a query in MariaDB 10.3 database where there is a field called "expiration_date" that stores a unix timestamp, but if there is no data in the field the default is set to "0".
I’m trying to use a WHERE clause to check the current date against the expiration_date to filter out any records that are past the expiration_date. Below is what I have.
SELECT entry_id, title, (CASE WHEN expiration_date = "0" THEN CURDATE() + INTERVAL 1 DAY ELSE FROM_UNIXTIME(expiration_date, "%Y-%m-%d") END) AS expiration_date
FROM channel_titles
WHERE CURDATE() < expiration_date
This returns and empty result set… what am I missing?
2
Answers
You’re trying to use an alias of
expiration_date
from yourCASE
statement in yourWHERE
clause.Two problems with this:
WHERE
clause. Refer to this post here.WHERE
clause is not throwing an error regarding your alias, itscomparing the current date to the
expiration_date
column in the table,thus, throwing off your expected result.
Solutions:
If you want to use the alias in your
WHERE
clause, there are a few options for you to force SQL to handle theSELECT
before theWHERE
clause.operation by using parentheses:
SELECT
itFROM
theCTE
:WHERE
clause and plug in the logic from yourSELECT
statement directly into yourWHERE
clause. However, this may appear redundant from a readability perspective; also, extra processing should be considered when using this approach as well, but if you have a small data set this method will work just fine:Input:
Output:
db<>fiddle here.
There’s a very simple solution to this and it only requires you to change two things from your original query:
The first part is your column (
CASE
expression) alias – you should define your alias with something not similar to any of the column names present in the table. From your query, you have a columnexpiration_date
in your table and you also set an alias for yourCASE
expression withexpiration_date
as well and since you’re usingWHERE
, the query will definitely do the lookup based on your tableexpiration_date
column instead of yourCASE
expression. Rename that alias to something likeexp_date
… but doingWHERE exp_date ...
will return you an error. Refer to the second point below.The second part is your
WHERE
– since you’re doing lookup from aCASE
expression (or perhaps custom generated value/column) with newly assigned alias ofexp_date
, you can’t use it inWHERE
.. well, unless you make the query as a subquery/derived table then do theWHERE
outside.. but you don’t need to. You only need to changeWHERE
toHAVING
and you should be able to use theexp_date
and get your result.So, with those two changes, your query should be something like this:
demo fiddle