批量插入数据
修改时间段,将此时间段内数据的时间增加一个月插入到表里
-- 坝顶沉降
Insert into dtjc_bd(bd_sbbh,bd_y,bd_time)
select bd_sbbh,bd_y,DATE_ADD(bd_time,INTERVAL 1 MONTH)
from dtjc_bd c
WHERE c.bd_time between '2021-09-06 10:07:35' and '2021-09-13 14:07:35'
-- 表面位移
Insert into dtjc_bmwy(bmwy_sbbh,bmwy_x,bmwy_y,bmwy_time) select bmwy_sbbh,bmwy_x,bmwy_y,DATE_ADD(bmwy_time,INTERVAL 1 MONTH) from dtjc_bmwy c
WHERE c.bmwy_time between '2021-09-06 10:07:35' and '2021-09-13 14:07:35'
取时间最新一条数据
SELECT *
FROM table
WHERE time in
(SELECT max(time) FROM table )
取24小时内最旧一条数据
SELECT *
FROM table
WHERE time in
(SELECT min(time) FROM table WHERE time>=date_sub(NOW(),interval 1 day))
获得每天最新一条数据
where限制时间在当天到一周前
SELECT *
FROM table a
JOIN ( SELECT DATE_FORMAT( time, '%Y-%m-%d' ) date, MAX( time ) time
FROM table
GROUP BY date ) b ON b.time = a.time
WHERE DATE_FORMAT(a.time,'%Y-%m-%d') BETWEEN DATE_FORMAT( date_sub(NOW(),interval 7 day), '%Y-%m-%d' ) AND DATE_FORMAT( NOW(), '%Y-%m-%d' )
ORDER BY
a.time DESC
时间段内按每小时添加数据
DROP PROCEDURE if EXISTS ins_data -- 删除存储过程ins_data
DELIMITER // -- 定义结束符号
CREATE PROCEDURE ins_data() -- 创建存储过程ins_data
BEGIN -- 开始标志
DECLARE start_date DATETIME; -- 声明DATETIME类型的变量start_date
DECLARE end_date DATETIME;
DECLARE i int;-- 没用到的int变量i
SET start_date = '2021-06-10 11:41:11.01'; -- 变量赋值
SET end_date = '2021-06-10 14:41:11.01';
WHILE start_date < end_date DO -- while循环
set start_date = DATE_ADD(start_date,INTERVAL 1 hour); -- 使start_date每循环1次+1小时
-- *******************执行需要的SQL语句*************************
INSERT INTO dtjc_bmwy(bmwy_sbbh,bmwy_time,bmwy_x,bmwy_y)
VALUES ('GPS-6',start_date,ROUND(RAND()*(-0.2-0.2)+0.2,1) +0.4,ROUND(RAND()*(-0.2-0.2)+0.2,1)+0.2 );
INSERT INTO dtjc_bmwy(bmwy_sbbh,bmwy_time,bmwy_x,bmwy_y)
VALUES ('GPS-7',start_date,ROUND(RAND()*(-0.2-0.2)+0.2,1) +1.0,ROUND(RAND()*(-0.2-0.2)+0.2,1)+0.2 );
INSERT INTO dtjc_bmwy(bmwy_sbbh,bmwy_time,bmwy_x,bmwy_y)
VALUES ('GPS-8',start_date,ROUND(RAND()*(-0.2-0.2)+0.2,1) +0.4,ROUND(RAND()*(-0.2-0.2)+0.2,1)+0.9 );
-- *******************执行需要的SQL语句end***********************
end WHILE; -- 结束循环标志
END // -- 结束标志
call ins_data() -- 执行存储过程
给数据库普通用户赋权后,用户需重新连接数据库才生效