常用mysql根据时间查询数据

mysql

-- 查询最新一条数据
SELECT * FROM apple ORDER BY id DESC LIMIT 1;
-- 今天  
select * from apple where to_days(data_time) = to_days(now()); 
-- 昨天  
select * from apple where to_days(NOW()) - TO_DAYS(data_time) <= 1;  
-- 近7天  
select * from apple where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(data_time);  
-- 上周
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;
-- 近30天  
SELECT * FROM apple where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(data_time);
-- 本月  
SELECT * FROM apple WHERE DATE_FORMAT( data_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );
-- 上一月  
SELECT * FROM apple WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( data_time, '%Y%m' ) ) =1; 
-- 查询本季度数据  
select * FROM apple where QUARTER(data_time)=QUARTER(now()); 
-- 查询上季度数据  
select * FROM apple where QUARTER(data_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  
-- 查询本年数据  
select * FROM apple where YEAR(data_time)=YEAR(NOW());
-- 根据时间段查询数据
select * from table_name where date(data_time) between '2021-09-01 00:00:00' and '2021-09-01 23:59:59' 

Influxdb

select * from mymeasurement where time >='2018-07-16T10:00:00Z' tz('Asia/Shanghai')

未完待续···········