下面是一个 mysql 函数，它计算两个日期之间的年、月、日、小时、分钟和秒的持续时间。
mysql> drop function if exists duration;query ok, 0 rows affected, 1 warning (0.00 sec)mysql> drop function if exists label123;query ok, 0 rows affected, 1 warning (0.00 sec)mysql> delimiter //mysql> create function duration( dtd1 datetime, dtd2 datetime ) returns char(128) -> begin -> declare yyr,mon,mmth,dy,ddy,hhr,m1,ssc,t1 bigint; -> declare dtmp datetime; -> declare t0 timestamp; -> set yyr = timestampdiff(year,dtd1,dtd2); -> set mon = timestampdiff(month,dtd1,dtd2); -> set mmth = mon mod 12; -> set dtmp = adddate(dtd1, interval mon month); -> set dy = timestampdiff(day,dtd1,dtd2); -> set ddy = timestampdiff(day,dtmp,dtd2); -> set t0 = timestampadd(day,dy,dtd1); -> set t1 = time_to_sec(timediff(dtd2,t0)); -> set hhr = floor(t1/3600); -> set m1 = floor(t1/60) - 60*hhr; -> set ssc = t1 - 3600*hhr - 60*m1; -> return concat( label123(yyr,'year'), label123(mmth,'month'), -> label123(ddy,'day'), label123(hhr,'hour'), -> label123(m1,'min'), label123(ssc,'sec') -> ); -> end; -> //query ok, 0 rows affected (0.00 sec)mysql> create function label123( ival int, clabel char(16) ) returns varchar(24) -> return concat( ival, ' ', clabel, if(ival=1,' ','s ') ); //query ok, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> select duration('2000-08-04 06:09:46', '2011-07-01 05:05:36')as 'duration';+-----------------------------------------------------+| duration |+-----------------------------------------------------+| 10 years 10 months 26 days 22 hours 55 mins 50 secs |+-----------------------------------------------------+1 row in set (0.00 sec)
以上就是我们如何创建一个 mysql 函数来找出年、月、日、小时、分钟和秒的持续时间？的详细内容。