mysql timespan_在MySQL中计算Timespan

此查询将显示2011年2月1日午夜的确切天数，小时数，分钟数和秒数：

SELECT

TRIM(REPLACE(CONCAT(

IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),

IF(hr=0,'',IF(hr=1,'1 hr ', CONCAT(hr,' hrs '))),

IF(mn=0,'',IF(mn=1,'1 min ',CONCAT(mn,' mins '))),

IF(sc=0,'',IF(sc=1,'1 sec ',CONCAT(sc,' secs ')))),' ',' '))

TimeDisplay

FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc

FROM (SELECT dy,hr,FLOOR((sec_aaa - dy*86400 - hr*3600)/60) mn,sec_aaa sec_aaaa

FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa

FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa

FROM (SELECT (UNIX_TIMESTAMP() - UNIX_TIMESTAMP('2011-02-01 00:00:00')) sec_a)

A) AA) AAA) AAAA) B;只需将'2011-02-01 00:00:00'替换为您想要的任何日期时间值或表列名称即可。

试一试 ！！！

更新2011-10-06 13:38美国东部时间

我写了一个你可以调用的存储函数，它将为你处理：

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`GetTimeDisplay` $$

CREATE FUNCTION `test`.`GetTimeDisplay` (GivenTimestamp TIMESTAMP)

RETURNS VARCHAR(32)

DETERMINISTIC

BEGIN

DECLARE rv VARCHAR(32);

DECLARE diff BIGINT;

SET diff = UNIX_TIMESTAMP() - UNIX_TIMESTAMP(GivenTimestamp);

SELECT

TRIM(REPLACE(CONCAT(

IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),

IF(hr=0,'',IF(hr=1,'1 hr ', CONCAT(hr,' hrs '))),

IF(mn=0,'',IF(mn=1,'1 min ',CONCAT(mn,' mins '))),

IF(sc=0,'',IF(sc=1,'1 sec ',CONCAT(sc,' secs ')))),' ',' '))

INTO rv

FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc

FROM (SELECT dy,hr,FLOOR((sec_aaa - dy*86400 - hr*3600)/60) mn,sec_aaa sec_aaaa

FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa

FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa

FROM (SELECT ABS(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(GivenTimestamp)) sec_a)

A) AA) AAA) AAAA) B;

IF diff = 0 THEN

SET rv = '0 secs';

END IF;

IF diff < 0 THEN

SET rv = CONCAT(rv,' From Now');

END IF;

IF diff > 0 THEN

SET rv = CONCAT(rv,' Ago');

END IF;

RETURN rv;

END $$

DELIMITER ;您可以像这样重写查询：

$query = "select country, rprice as regPrice, mprice as midPrice, pprice as prePrice, saddress as streetAddress,

_id as ID, lat, lng, sname as Name, logo, admin_level_1 as state, locale as city, test.GetTimeDisplay(rdate) as regDate,

test.GetTimeDisplay(mdate) as midDate, test.GetTimeDisplay(pdate) as preDate,

format((acos(sin(radians($lat1)) * sin(radians(lat)) + cos(radians($lat1)) *

cos(radians(lat)) * cos(radians($lng1) - radians(lng))) * 6378),1) as distance from stationDetails where

(acos(sin(radians($lat1)) * sin(radians(lat)) + cos(radians($lat1)) * cos(radians(lat)) *

cos(radians($lng1) - radians(lng))) * 6378) <= $rad order by $sort asc, $type asc";您可能希望将存储的函数移动到另一个数据库。我的代码将存储的函数放在测试数据库中。

试一试 ！！！