I have a DATETIME value "some_date" that can be null in my database. When I try the following query it returns nothing for those rows with date is null:
SELECT *
FROM tabel
WHERE DATE_ADD(some_date, INTERVAL some_seconds SECOND) < NOW()
Can I edit this query so that the DATE_ADD() will produce a DATE even when some_date is null?
You could use COALESCE to convert the NULL dates to something else, for example:
select *
from table
where date_add(coalesce(some_date, now()), interval some_seconds second) < now()
COALESCE returns its first non-NULL argument:
COALESCE(value, ...)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
So you'd just need to decide which date you wanted to use in place of a NULL some_date.
If your replacement for a NULL some_date is going to be far enough into the past that the test will always pass then you could use:
where some_date is null
or date_add(some_date, interval some_seconds second) < now()
and if it will always fail, then:
where some_date is not null
and date_add(some_date, interval some_seconds second) < now()
some_date is not null and date_add(some_date, .... instead; if it will always pass then some_date is null or date_add(some_date, .... That would get around glglgl's index issue.It is probably a little clearer to use the following query:
SELECT *
FROM tabel
WHERE some_date IS NULL OR DATE_ADD(some_date, INTERVAL some_seconds SECOND) < NOW()
