MySQL query to return 1 and 0 based on difference between dates

Question

In MySQL Database I have a table ABC that consists of a column 'LastDate'.

LastDate which has datatype as DATETIME. The default value for this 'NULL'

I need to write a query for the table which would Return '1' in these cases. 1) If DATEDIFF(CURRENT_TIME,LastDate) is >15 or if DATEDIFF(CURRENT_TIME,LastDate) is NULL(i.e defaultVal).

return '0' if DATEDIFF(CURRENT_TIME,LastDate) is <15.

I tried to write an SQL query for this but was unable to do it. Please help me write this Query. Thanks in advance.

Answer 1

You can be explicit about your logic:

select t.*,
       (case when DATEDIFF(CURRENT_TIME, LastDate) > 15 or
                  LastDate is null
             then 1 else 0
        end) as flag
from t;

This can be simplified to:

select t.*,
       coalesce(DATEDIFF(CURRENT_TIME, LastDate) <= 15, 1) as flag
from t;