MySQL DATE_ADD does not work

Question

I have two columns: beginTime and duration. I'm trying to calculate the endTime. The problem is that i get empty results back.

I have tried several things.

DATE_ADD(startTime, INTERVAL `duration` MINUTE) AS endTime

DATE_ADD(startTime, INTERVAL '10' MINUTE) AS endTime

DATE_ADD(startTime, INTERVAL 10 MINUTE) AS endTime

But everytime the result is empty. startTime is not empty. It is a time field in the format 09:00:00.

Can anyone help me with this?

Answer 1

This works for me:

SELECT DATE_ADD(`startTime`, INTERVAL 10 MINUTE) AS endTime FROM `users` WHERE 1

Answer 2

In DATE_ADD first parameter supposed to be Date field not Time field. Can you try changing the start time to Date format ('0000-00-00 00:00:00') and try?

Answer 3

DATE_ADD will return NULL if the time format is incorrect, along with a warning:

mysql> \W
Show warnings enabled.
mysql> SELECT DATE_ADD('25:00:00', INTERVAL 10 MINUTE) AS endTime
    -> ;
+---------+
| endTime |
+---------+
| NULL    |
+---------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Incorrect datetime value: '25:00:00'

Also note that 'hh:mm:ss' is not a correct datetime format. You need to have year, month and day also.

Could you please enable warnings in MySQL and repeat the query with your data?