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

问题描述：

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.

在mysql数据库中添加到日期时间类型列的最佳方法是什么？我尝试使用DATE_ADD但它只在我添加单个日期/时间时才有效： p>

  DATE_ADD（start_date，INTERVAL：dayCount DAY）
  code>  pre>  
 
 这不起作用： p> 
 
 
  DATE_ADD（DATE_ADD（start_date，INTERVAL：dayCount DAY），INTERVAL：hourCount HOUR）
  code>  
 
 我尝试按照一些人的建议添加微秒： p> 
 
 
  $ startMicro = strtotime（$ startDiff-＆gt; format（“％Y-％  m-％d％H：％i：％s“））* 1000;  // 943920000000 
start = DATE_ADD（开始，INTERVAL：startMicro MICROSECOND）
  code>  pre> 
 
 我终于完成了它的工作原理：
 
 
 
 
：PHP：
 $ minutesDiff  =（strtotime（$ start） -  strtotime（$ row-＆gt; start））/ 60; 
in SQL STATEMENT：
 start = DATE_ADD（start，INTERVAL：minutesDiff MINUTE） p> 
 
 
 谢谢大家的帮助。 p> 
  div>

答

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)

答

You can use this syntax for the purpose:

start_date + INTERVAL :dayCount DAY + 
             INTERVAL :hourCount HOUR + 
             INTERVAL :minCount MINUTE

The INTERVAL types are YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MICROSECOND.

Microseconds don't make any sense with MySQL versions before 5.6.4.

See here: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-add

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

相关推荐