I am trying to update a table column with below query.
UPDATE `prayer_times` SET `maghrib_jamat` = `maghrib` + .05;
I have two column maghrib_jamat and maghrib. In maghrib values are like 18:42 which is a time value with varchar type. I would like to add 5 minutes with maghrib and insert that value into maghrib_jamat column. I am getting error #1292 - Truncated incorrect DOUBLE value: '18:42'.
How can I do the task ?
You could cast the time value as time and use ADDTIME function to add the required minutes to it as the following:
UPDATE prayer_times
SET maghrib_jamat =
DATE_FORMAT(ADDTIME(CAST(maghrib AS TIME), '00:05:00'), '%H:%i')
The use of DATE_FORMAT is to remove the seconds part from the casted time.
See a demo.
You could do it this way:
declare @t datetime = getdate()
select @t , @t + '00:05' , dateadd(minute, 5, @t)
Code illustrate different ways of doing it :)
