Search code examples
mysqlsqlnulldateadd

MySQL DATE_ADD() when DATE is null

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?

Solution

  • 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()