Update Mysql Table column using another column-CodePudding

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 ?

CodePudding user response：

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.

CodePudding user response：

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 :)