如何计算两个日期之间的差异，格式为YYYY-MM-DD hh: mm: ss，并以秒或毫秒为单位得到结果?
当前回答
你可以简单地这样做:
SELECT (end_time - start_time) FROM t; -- return in Millisecond
SELECT (end_time - start_time)/1000 FROM t; -- return in Second
其他回答
select
unix_timestamp('2007-12-30 00:00:00') -
unix_timestamp('2007-11-30 00:00:00');
如果您正在使用DATE列(或者可以将它们转换为日期列)，请尝试DATEDIFF()，然后乘以24小时、60分钟、60秒(因为DATEDIFF返回的天数不同)。从MySQL:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
例如:
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60
SELECT TIMESTAMPDIFF(HOUR,NOW(),'2013-05-15 10:23:23')
calculates difference in hour.(for days--> you have to define day replacing hour
SELECT DATEDIFF('2012-2-2','2012-2-1')
SELECT TO_DAYS ('2012-2-2')-TO_DAYS('2012-2-1')
你可以简单地这样做:
SELECT (end_time - start_time) FROM t; -- return in Millisecond
SELECT (end_time - start_time)/1000 FROM t; -- return in Second
或者，您可以使用TIMEDIFF函数
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
'-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001' , '2008-12-30 01:01:01.000002');
'46:58:57.999999'
