【群答疑20210525-3】MySQL限制时间是当前月,或者月区间查询
群问题:finebi里写sql 怎么限制时间是当前月 或者几月到几月
经过确认,是MySQL
先抛出网上例子
本地环境验证:
1、查询当月数据 (date字段为varchar类型)
2、月份区间(create_time字段为varchar类型)后面改了字段名
月份区间,方法2
查看字段类型,desc 表名;
如果是datetime类型,方法1:月区间直接比较 left截取
如果是datetime类型,方法2:月区间间接比较 year 和month函数
中间涉及所有练习代码
-- SELECT * from TestUsers1 WHERE name like 'user_8%' ; -- UPDATE TestUsers1 -- SET date=NOW() -- WHERE name like 'user_8%'; -- UPDATE TestUsers1 -- SET create_time='2021-04-26 19:00:00' -- WHERE name = 'user_129Yu6z4'; -- UPDATE TestUsers1 -- SET create_time='2021-01-26 19:00:00' -- WHERE name = 'user_12vV5aQG'; -- UPDATE TestUsers1 -- SET create_time='2021-03-26 19:00:00' -- WHERE name = 'user_12VoT0mp'; -- UPDATE TestUsers1 -- SET create_time='2021-02-26 19:00:00' -- WHERE name = 'user_12rFuaD0'; -- SELECT str_to_date(create_time,'%Y-%m-%d') from TestUsers1 ; SELECT * from TestUsers1 WHERE DATE_FORMAT(create_time, '%Y-%m') BETWEEN '2021-01' and '2021-04'; SELECT * from TestUsers1 WHERE DATE_FORMAT(create_time, '%Y-%m') = '2021-02'; SELECT * from TestUsers1 WHERE left(create_time, 7) BETWEEN '2021-01' and '2021-04'; desc TestUsers1; SELECT * from TestUsers1 WHERE name ='user_129Yu6z4'; SELECT * from TestUsers1 WHERE name in ('user_129Yu6z4','user_12vV5aQG','user_12VoT0mp','user_12rFuaD0'); UPDATE TestUsers1 SET create_time='2021-05-26 19:00:00' WHERE name = 'user_12vV5aQG'; SELECT * from TestUsers1 WHERE name like 'user_12%' ; -- desc TestUsers1; -- alter table TestUsers1 change date create_time varchar(25) ; -- CREATE TABLE TestUsers2 LIKE TestUsers1; -- INSERT INTO TestUsers2 SELECT * FROM TestUsers1; -- SELECT * FROM TestUsers2; select DISTINCT CHAR_LENGTH(create_time) from TestUsers1; -- 修改为datetime ALTER TABLE TestUsers1 MODIFY create_time datetime; SELECT * from TestUsers1 WHERE left(create_time, 7) BETWEEN '2021-01' and '2021-04'; SELECT * from TestUsers1 WHERE year(create_time)=2021 and month(create_time) BETWEEN 1 and 4;
-------------------------------
********厚德达理,励志勤工********
-------------------------------