Update only Time in a mysql DateTime field

Question

How can I update only the time in an already existing DateTime field in MySQL? I want the date to stay the same.

Answer 1

UPDATE myTable
SET myDateTime = ADDTIME(myDateTime, @myTimeSpan)
WHERE id = @id;

For exact syntax of function, see this.

Answer 2

UPDATE `table`
SET time = ADDTIME(time, INTERVAL 13 Hour);

Answer 3

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.

Answer 4

UPDATE myTable
SET myDateTime = ADDTIME(DATE(myDateTime), @myTimeSpan)
WHERE id = @id;

Documented on MySQl date functions MySQL docs

Answer 5

This should do the trick:

UPDATE t1 SET DateTimeField = CONCAT(DATE(DateTimeField),' 12:34:56');