Ishu Bhardwaj Ishu Bhardwaj - 7 months ago 17
SQL Question

set column values equals to columnB minus columnA

I want my MySql Column 'Time' to be update as Column 'End_Time' Minus Column 'Start_Time'

Note: End_Time & Start_Time are in DATETIME format....

Thanks

Answer

Try this method,

SELECT SEC_TO_TIME(TIMESTAMPDIFF(second,Start_Time,End_Time)) --include sec
or
SELECT SEC_TO_TIME(TIMESTAMPDIFF(minute,Start_Time,End_Time)*60) --diff in minute

eg

SELECT SEC_TO_TIME(TIMESTAMPDIFF(minute,'2016-05-04 10:00:00','2016-05-04 11:29:00')*60)

Here TIMESTAMPDIFF(minute will return the diff in minutes and mutiple it *60 to get seconds, and use SEC_TO_TIME to convert from sec to Time.