PHP转SQL：给日期字段加天数写入新字段的问题咨询

Got it, let's sort out this date calculation issue you're facing. The problem with your current query is that adding a raw number to a date value doesn't work the way you expect for multi-day increments—SQL treats that as a numeric addition instead of a proper date operation, which is why you're getting that invalid 00:00:000 value in the due field.

Here's how to correctly add days (or any time interval) to your date field:

Recommended Method: Use DATE_ADD() Function #

This is the standard, reliable way to handle date arithmetic in MySQL. It automatically handles month/year rollovers and preserves valid date formats.

For your specific example of adding 2 days to 29/03/2018 to get 31/03/2018, your query should look like this:

$sq2 = "UPDATE Tickets SET due = DATE_ADD(`date`, INTERVAL 2 DAY);";

You can adjust the interval to any value you need:

• Add 2 hours (replace your old +20000 hack with this proper syntax):

  UPDATE Tickets SET due = DATE_ADD(`date`, INTERVAL 2 HOUR);
  

• Add a combination of days and hours:

  UPDATE Tickets SET due = DATE_ADD(`date`, INTERVAL '3 5' DAY_HOUR);
  

Alternative: Use ADDDATE() Function #

This is an alias for DATE_ADD() and works the same way. You can even use a simplified syntax for just days:

$sq2 = "UPDATE Tickets SET due = ADDDATE(`date`, 2);"; -- Adds 2 days directly

Important Notes to Avoid Issues #

• Check Your Field Type: Make sure the due column is set to DATE, DATETIME, or TIMESTAMP type. If it's set to TIME, it can only store time values (not full dates), which is why you're seeing 00:00:000.
• Skip the date() Wrapper (If Needed): If your original date field is already a DATETIME or TIMESTAMP, you don't need to wrap it in date()—this will keep the time portion intact. For example, 2018-03-29 14:30:00 + 2 days becomes 2018-03-31 14:30:00.

内容的提问来源于stack exchange，提问作者Carl Hussain