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

最新推荐文章于 2024-06-25 15:05:43 发布

原创最新推荐文章于 2024-06-25 15:05:43 发布 · 921 阅读

0 ·

CC 4.0 BY-SA版权

mysql 同时被 3 个专栏收录

35 篇文章

订阅专栏

SQL

33 篇文章

订阅专栏

navicat

5 篇文章

订阅专栏

本文介绍了如何使用SQL过程来计算两个日期之间的详细时间差，并提供了一个实用的距离计算函数。通过这些函数，可以得到如“几分钟前”、“几小时前”等人性化的表述方式，同时还包括了经纬度坐标间的距离计算。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

效果图：

结果图：