Comment
Author: Admin | 2025-04-28
If you have MySql version prior than 5.6 you don't have TIMESTAMPDIFF. So,I wrote my own MySql function to do this. Accets %i or %m for minutes and %h for hours. You can extend it.Example of usage: SELECT MYTSDIFF('2001-01-01 10:44:32', '2001-01-01 09:50:00', '%h')Here goes the function. Enjoy: DROP FUNCTION IF EXISTS MYTSDIFF; DELIMITER $$ CREATE FUNCTION `MYTSDIFF`( date1 timestamp, date2 timestamp, fmt varchar(20)) returns varchar(20) DETERMINISTIC BEGIN declare secs smallint(2); declare mins smallint(2); declare hours int; declare total real default 0; declare str_total varchar(20); if date1 > DATE_ADD( date2, interval 30 day) then return '999999.999'; /* OUT OF RANGE TIMEDIFF */ end if; select cast( time_format( timediff(date1, date2), '%s') as signed) into secs; select cast( time_format( timediff(date1, date2), '%i') as signed) into mins; select cast( time_format( timediff(date1, date2), '%H') as signed) into hours; set total = hours * 3600 + mins * 60 + secs; set fmt = LOWER( fmt); if fmt = '%m' or fmt = '%i' then set total = total / 60; elseif fmt = '%h' then set total = total / 3600; else /* Do nothing, %s is the default: */ set total = total + 0; end if; select cast( total as char(20)) into str_total; return str_total; END$$ DELIMITER ;
Add Comment