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字段中的时间？我希望日期保持不变。
10 个解决方案
#1
41
Try this:
尝试这个：
UPDATE yourtable SET yourcolumn=concat(date(yourcolumn), ' 21:00:00') WHERE Id=yourid;
#2
10
Try this:
尝试这个：
UPDATE t1 SET DateTimeField = CONCAT(DATE(DateTimeField),' 12:34:56');
#3
4
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（）。
#4
2
UPDATE myTable
SET myDateTime = ADDTIME(DATE(myDateTime), @myTimeSpan)
WHERE id = @id;
Documented on MySQl date functions MySQL docs
记录MySQl日期函数MySQL文档
#5
1
UPDATE myTable
SET myDateTime = ADDTIME(myDateTime, @myTimeSpan)
WHERE id = @id;
For exact syntax of function, see this.
有关函数的确切语法，请参阅此。
#6
1
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';
#7
0
UPDATE `table`
SET time = ADDTIME(time, INTERVAL 13 Hour);
#8
0
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值，以便可以替换整个值。
#9
0
MySQL DEV page shows functions like subtime and 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
#10
0
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 = ...
#1
41
Try this:
尝试这个：
UPDATE yourtable SET yourcolumn=concat(date(yourcolumn), ' 21:00:00') WHERE Id=yourid;
#2
10
Try this:
尝试这个：
UPDATE t1 SET DateTimeField = CONCAT(DATE(DateTimeField),' 12:34:56');
#3
4
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（）。
#4
2
UPDATE myTable
SET myDateTime = ADDTIME(DATE(myDateTime), @myTimeSpan)
WHERE id = @id;
Documented on MySQl date functions MySQL docs
记录MySQl日期函数MySQL文档
#5
1
UPDATE myTable
SET myDateTime = ADDTIME(myDateTime, @myTimeSpan)
WHERE id = @id;
For exact syntax of function, see this.
有关函数的确切语法，请参阅此。
#6
1
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';
#7
0
UPDATE `table`
SET time = ADDTIME(time, INTERVAL 13 Hour);
#8
0
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值，以便可以替换整个值。
#9
0
MySQL DEV page shows functions like subtime and 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
#10
0
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 = ...