sql 时间范围查询

use bq_person_mont

alter table bq_person_month modify column cj_month INTEGER;

SELECT * from bq_person_month

SELECT COUNT(*) FROM bq_person_month where user_id = ‘1001’

select GETDATE()

select COUNT(*) from bq_person_month where create_time > date_add(NOW(),interval -1 MONTH ) AND user_id = ‘1001’ ;

6
5
4
3
2
1

select * from bq_person_month where period_diff(date_format(now() , ‘%Y%m’) , date_format(add_time, ‘%Y%m’)) =1;

SELECT * FROM bq_person_month WHERE DATEDIFF(create_time,NOW())<0 AND DATEDIFF(create_time,NOW())>-140

select * from table_name where trunc(column,‘mm’)=to_date(201304,‘yyyy/mm’);

SELECT * FROM bq_person_month WHERE DATEDIFF(m, NOW(), GETDATE()) <=1

SELECT date_add(NOW(),interval -30 DAY )

SELECT count(*) FROM bq_person_month WHERE date_format(create_time, ‘%Y %m’) = date_format(DATE_SUB(curdate(), INTERVAL 6 MONTH),’%Y %m’) and user_id = ‘1001’

select COUNT(*) from bq_object where date_sub(curdate(),interval 1 day)<= create_time and cjr_id = ‘1001’;

select count(*),DATE_FORMAT(bq_object.create_time,’%e’) as e from table bq_object
where DATE_FORMAT(bq_object.create_time,’%m’)=‘10’
group by e

select DATE_FORMAT(create_time,’%Y-%m-%d’) days,count(*) count from bq_object where cjr_id = ‘1001’ group by days ;

SELECT DATE_FORMAT(create_time,’%Y-%m-%d’) AS data_date,COUNT(* ) AS num FROM bq_object
WHERE create_time >= date_sub(curdate(),interval 10 day) and cjr_id = ‘1001’ GROUP BY data_date;

SELECT d.date, count from(
select date_add(curdate(),interval @i:=@i-1 day) as date
from (
select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
) as tmp,
(select @i:= 0) t
)d
left join(

select count(*) count,DATE_FORMAT(create_time,’%Y-%m-%d’) create_time

from bq_object

where create_time >= date_sub(curdate(),interval 10 day) and cjr_id = ‘1002’

group by DATE_FORMAT(create_time,’%Y-%m-%d’)

) T on T.create_time = d.date

GROUP BY d.date

select date_add(curdate(),interval @i:=@i-1 day) as date
from (
select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
) as tmp,
(select @i:= 0) t

select * from bq_object where date_sub(curdate(),interval 10 day)<= create_time;

select date_sub(curdate(),interval 10 day)

sELECT COUNT(*) FROM bq_person_month WHERE TO_CHAR(T_RKSJ,‘YYYY-MM’)=TO_CHAR(ADD_MONTHS(SYSDATE,-1),‘YYYY-MM’)

SELECT date_add(NOW(),interval -1 MONTH )

SELECT curdate()

SELECT NOW()