如何使用date_add将年，月，日，小时，分钟，秒添加到mysql日期？

Question

What is the best way to add to a datetime type column in mysql database? I tried using DATE_ADD but it only works when I add single date/time like:

DATE_ADD(start_date, INTERVAL :dayCount DAY)

This does NOT Work:

DATE_ADD(DATE_ADD(start_date, INTERVAL :dayCount DAY), INTERVAL :hourCount HOUR)

I tried adding microseconds as suggested by some:

$startMicro = strtotime($startDiff->format("%Y-%m-%d %H:%i:%s")) * 1000; // 943920000000
start=DATE_ADD(start, INTERVAL :startMicro MICROSECOND)

I DID THIS IN THE END AND IT WORKS: in PHP: $minutesDiff = (strtotime($start) - strtotime($row->start)) / 60; in SQL STATEMENT: start=DATE_ADD(start, INTERVAL :minutesDiff MINUTE)

Thanks all for the help.

Answer 1

Convert the total time being added to the smallest increment of time you want to add, so rather than 1 day, 1 hour, you'd do 25 hours. Instead of 1 hour, 1 minute, 1 second, you'd do 3601 seconds, etc.

You can nest them if desired, but your syntax is incorrect, missing a comma before 2nd INTERVAL, should be:

DATE_ADD(DATE_ADD(start_date, INTERVAL :dayCount DAY), INTERVAL :hourCount HOUR)