利用SQL函数实现几天前、两点之间的距离

diffDate

BEGIN  DECLARE n_lost_day int;  DECLARE n_lost_hours int;  DECLARE n_lost_minute int;  set n_lost_day = date(new_date)-date(old_date);  set n_lost_hours = hour(new_date)-hour(old_date);  set n_lost_minute = minute(new_date)-minute(old_date);  if(n_lost_minute<0) THEN    set n_lost_minute = n_lost_minute+60;    set n_lost_hours = n_lost_hours-1;  END IF;  if(n_lost_hours<0) THEN    set n_lost_hours = n_lost_hours+24;    set n_lost_day = n_lost_day-1;  END IF;  return concat(n_lost_day,"天",n_lost_hours,"小时",n_lost_minute,"分");END

diffDateHour

BEGIN    DECLARE n_lost_day INT;    DECLARE n_lost_hours INT;    DECLARE n_lost_minute INT;    SET n_lost_day = to_days(new_date) - to_days(old_date);    SET n_lost_hours = hour(new_date) - hour(old_date);    SET n_lost_minute = minute(new_date) - minute(old_date);    IF (n_lost_minute < 0)    THEN      SET n_lost_minute = n_lost_minute + 60;      SET n_lost_hours = n_lost_hours - 1;    END IF;    IF (n_lost_hours < 0)    THEN      SET n_lost_hours = n_lost_hours + 24;      SET n_lost_day = n_lost_day - 1;    END IF;    IF (n_lost_day = 0 AND n_lost_hours = 0 AND n_lost_minute = 0)    THEN      RETURN "刚刚";    END IF;    IF (n_lost_day = 0 AND n_lost_hours = 0)    THEN      RETURN concat(n_lost_minute, "分前");    END IF;    IF (n_lost_day = 0)    THEN      RETURN concat(n_lost_hours, "小时前");    END IF;    IF (n_lost_hours = 0)    THEN      RETURN concat(n_lost_day, "天前");    END IF;    RETURN concat(n_lost_day, "天", n_lost_hours, "小时前");  END

diffDateMinute

BEGIN    DECLARE n_lost_day INT;    DECLARE n_lost_hours INT;    DECLARE n_lost_minute INT;    SET n_lost_day = to_days(new_date) - to_days(old_date);    SET n_lost_hours = hour(new_date) - hour(old_date);    SET n_lost_minute = minute(new_date) - minute(old_date);    IF (n_lost_minute < 0)    THEN      SET n_lost_minute = n_lost_minute + 60;      SET n_lost_hours = n_lost_hours - 1;    END IF;    IF (n_lost_hours < 0)    THEN      SET n_lost_hours = n_lost_hours + 24;      SET n_lost_day = n_lost_day - 1;    END IF;    IF (n_lost_day=0) THEN      IF (n_lost_hours=0) THEN        if(n_lost_minute=0) THEN          RETURN "1分钟";          ELSE          RETURN concat(n_lost_minute,"分钟");        END IF;        ELSE        RETURN concat(n_lost_hours,"小时",n_lost_minute,"分钟");      END IF;      ELSE      IF (n_lost_hours=0) THEN        if(n_lost_minute=0) THEN          RETURN concat(n_lost_day,"天");          ELSE          RETURN concat(n_lost_day,"天",n_lost_minute,"分钟");        END IF;        ELSE        RETURN concat(n_lost_day,"天",n_lost_hours,"小时",n_lost_minute,"分钟");      END IF;    END IF;  END

两点之间的距离 select GetDiscover(23.12398694,113.32286598, 22.99505266, 113.27586664);

BEGIN    DECLARE pk DECIMAL(20, 16);    DECLARE a1 DECIMAL(20, 16);    DECLARE a2 DECIMAL(20, 16);    DECLARE b1 DECIMAL(20, 16);    DECLARE b2 DECIMAL(20, 16);    DECLARE t1 DECIMAL(20, 16);    DECLARE t2 DECIMAL(20, 16);    DECLARE t3 DECIMAL(20, 16);    DECLARE tt DECIMAL(20, 16);    SET pk = 180 /PI();    SET a1 = lat_a / pk;    SET a2 = lng_a / pk;    SET b1 = lat_b / pk;    SET b2 = lng_b / pk;    SET t1 = cos(a1) * cos(a2) * cos(b1) * cos(b2);    SET t2 = cos(a1) * sin(a2) * cos(b1) * sin(b2);    SET t3 = sin(a1) * sin(b1);    SET tt = acos(t1 + t2 + t3);    #RETURN concat("pk:",pk," a1:",a1," a2:",a2," b1:",b1," b2:",b2," t1:",t1," t2:",t2," t3:",t3," tt:",tt);    RETURN 6371000 * tt;  END

调用函数

-- -- 调用两点距离函数select GetDiscover(23.12398694,113.32286598, 22.99505266, 113.27586664);-- -- 调用几天前函数（精确到小时）select diffDateHour(ifnull(finish_date,CURRENT_TIME), find_date) from message;-- -- 调用几天前函数（精确到分钟）select diffDateMinute(ifnull(finish_date,CURRENT_TIME), find_date) from message;

效果图：

2

结果图：