sql的日期函数

 常用日期函数：

select 
current_date() as now,
date_add(current_date(), interval -1 day) as yesterday,
datediff(current_date(),date_add(current_date(), interval -1 day)) as date_cha,
date_format(current_date(),'%Y/%m') as ym,
str_to_date('2020-05-01','%Y-%m-%d') as strdate1,
str_to_date('2020.05.01','%Y.%m.%d') as strdate2

 

例题： 

计算两个日期之间差的天数<90：datediff('2019-06-30',login_date)<=90

编写一个 SQL 查询，以查询从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30.

select user_id, min(activity_date) login_date 
from Traffic
where activity='login'
group by user_id

select login_date,count(user_id) user_count
from (select user_id, min(activity_date) login_date from Traffic
where activity='login'
group by user_id) t
where datediff('2019-06-30',login_date)<=90
group by login_date;