Adding Dates and Checking NULL values in MYSQL

Question

I've been trying to figure out a query which will do the followings:

Check last_service record and add delivery_frequency record which is day-based to next_service date field in mysql.
However, if last_service is NULL or Empty then update last_service to today's date and do the step 1 afterwards.

I know how to do the step one which is the following query:

UPDATE events SET next_service = DATE_ADD(last_service,INTERVAL 14 DAY) WHERE order_product_id = 3;

But somewhat I found Step 2nd trickier. Could some one show me how to do that?

I am consuming these with PHP but I don't want to handle this part with PHP doing double query or something.

You can think what I am looking for as following:

UPDATE events SET next_service = IF last_service IS NOT NULL OR '' THEN
DATE_ADD(last_service,INTERVAL 14 DAY) ELSE SET last_service = CURRENT_DATE AND
DATE_ADD(last_service,INTERVAL 14 DAY) END IF WHERE order_product_id = 3;

Dan Soap · Accepted Answer · 2012-02-06 00:41:51Z

4

UPDATE events
SET
   last_service = coalesce(last_service, CURRENT_DATE),
   next_service = DATE_ADD(coalesce(last_service, CURRENT_DATE), INTERVAL 14 DAY)
WHERE order_product_id = 3;

Coalesce returns the first non-null value, so that

coalesce('2011-12-31', CURRENT_DATE)

returns 2011-12-31

and

coalesce(null, CURRENT_DATE)

returns 2012-02-06 which is CURRENT_DATE

edited Feb 6, 2012 at 0:41

answered Feb 6, 2012 at 0:36

Dan Soap

10.3k2 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tarik Over a year ago

OK, seriously this is awesome how you came up with this answer. Looks like exactly what I've been looking for.

Collectives™ on Stack Overflow

Adding Dates and Checking NULL values in MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related