仅更新 mysql DateTime 字段中的时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1326548/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update only Time in a mysql DateTime field
提问by Martin
How can I update only the time in an already existing DateTime field in MySQL? I want the date to stay the same.
如何仅更新 MySQL 中现有 DateTime 字段中的时间？我希望日期保持不变。
回答by Dani
Try this:
尝试这个：
UPDATE yourtable SET yourcolumn=concat(date(yourcolumn), ' 21:00:00') WHERE Id=yourid;
回答by jp.
Try this:
尝试这个：
UPDATE t1 SET DateTimeField = CONCAT(DATE(DateTimeField),' 12:34:56');
回答by Jubstuff
I have solved in this way:
我是这样解决的：
UPDATE table
SET myDateTime = CONCAT_WS(' ',DATE(myDateTime), CURTIME())
WHERE id = @id;
Obviously you should change CURTIME()with your desired time.
显然你应该CURTIME()随着你想要的时间而改变。
回答by user151019
UPDATE myTable
SET myDateTime = ADDTIME(DATE(myDateTime), @myTimeSpan)
WHERE id = @id;
Documented on MySQl date functions MySQL docs
记录在 MySQl 日期函数MySQL 文档中
回答by KoNTrasT
Try this:
尝试这个：
UPDATE sms
SET entry_period_end_date= entry_period_end_date+INTERVAL 6 Hour
WHERE TIME(entry_period_end_date) = '06:00:00';
回答by colithium
回答by Zed
UPDATE `table`
SET time = ADDTIME(time, INTERVAL 13 Hour);
回答by Guffa
Well, exactly what you are asking for is not possible. The date and time components can't be updated separately, so you have to calculate the new DateTime value from the existing one so that you can replace the whole value.
好吧，正是您所要求的，这是不可能的。日期和时间组件无法单独更新，因此您必须从现有的 DateTime 值计算新的 DateTime 值，以便您可以替换整个值。
回答by orafaelreis
MySQL DEV page shows functions like subtimeand difftime
MySQL DEV 页面显示了像subtime和difftime
A sample code to back the time all posts in 3 hours is above:
上面的示例代码支持 3 小时内所有帖子的时间：
UPDATE tablepost SET datepost = SUBTIME( datepost , '0 3:0:0' );
Note that values 0 dont alter the respective field. Take care this code, use select first to test these function.
请注意，值 0 不会改变相应的字段。注意这段代码，首先使用 select 来测试这些功能。
Reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_subtime
参考：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_subtime
回答by Tommy Raun
Asuming you have a DATE field and TIME field and want to inject the time into the date, try this:
假设您有一个 DATE 字段和 TIME 字段，并且想要将时间注入日期，请尝试以下操作：
UPDATE mytable
SET mydatefield = ADDTIME( DATE_FORMAT(mydatefield,'%Y-%m-%d 00:00:00'), mydatefield)
WHERE myid = ...
