首先说明一下date()函数的格式：
date('Y-m-d',timestamp); //输出年-月-日
date('Y-m-d H:i:s',timestamp); //输出年-月-日 时:分：秒
php获取今天日期
date("Y-m-d",strtotime("today")); //strtotime(‘today’)输出今天的开始时间戳
date('Y-m-d 00:00:00'); 今日零点时间
或
date("Y-m-d",time()); //time()输出当前秒时间戳
//php获取昨天日期
date("Y-m-d",strtotime("-1 day")); 或 date("Y-m-d",strtotime("yesterday"));
date('Y-m-d H:i:s',strtotime($todaytime)-86400);//昨日零点时间
//php获取明天日期
date("Y-m-d",strtotime("+1 day")); 或 date("Y-m-d",strtotime("tomorrow "));
//php获取7天后日期
date("Y-m-d",strtotime("+7 day"));
//php获取30天后日期
date("Y-m-d",strtotime("+30 day"));
//php获取一周后日期
date("Y-m-d",strtotime("+1 week"));
//php获取一个月后日期
date("Y-m-d",strtotime("+1 month"));
//php获取一个月前日期
date("Y-m-d",strtotime("last month")); 或 date("Y-m-d",strtotime("-1 month"));
date('Y-m-d H:i:s',strtotime($todaytime)-3600*24*30);//30天前零点日期
//php获取一年后日期
date("Y-m-d",strtotime("+1 year"));
//php获取一周零两天四小时五分钟两秒后时间
date("Y-m-d H:i:s",strtotime("+1 week 2 days 4 hours 5 minute 2 seconds"));
//php获取下个星期四日期
date("Y-m-d",strtotime("next Thursday"));
//php获取上个周一日期
date("Y-m-d",strtotime("last Monday"));
//php获取今天起止时间戳
mktime(0,0,0,date('m'),date('d'),date('Y'));
mktime(0,0,0,date('m'),date('d')+1,date('Y'))-1;
//php获取昨天起止时间戳
mktime(0,0,0,date('m'),date('d')-1,date('Y'));
mktime(0,0,0,date('m'),date('d'),date('Y'))-1;
//php获取上周起止时间戳
mktime(0,0,0,date('m'),date('d')-date('w')+1-7,date('Y'));
mktime(23,59,59,date('m'),date('d')-date('w')+7-7,date('Y'));
//php获取本月起止时间戳
mktime(0,0,0,date('m'),1,date('Y'));
mktime(23,59,59,date('m'),date('t'),date('Y'));
获取近7天零点的时间戳
$time1=strtotime("today");//当前天0点时间戳
$time2=strtotime(date('Y-m-d',strtotime("-1 day")));//前一天0点时间戳
$time3=strtotime(date('Y-m-d',strtotime("-2 day")));//前两天0点时间戳
$time4=strtotime(date('Y-m-d',strtotime("-3 day")));//前三天0点时间戳
$time5=strtotime(date('Y-m-d',strtotime("-4 day")));//前四天0点时间戳
$time6=strtotime(date('Y-m-d',strtotime("-5 day")));//前五天0点时间戳
$time7=strtotime(date('Y-m-d',strtotime("-6 day")));//前六天0点时间戳
统计 7 天内每天的数量
时间字段时间戳格式：
# 统计 7 天每天关注的人数
SELECT
FROM_UNIXTIME( subscribe_date, '%Y-%m-%d' ) AS date,
COUNT( * ) AS count
FROM
sys_weixin_fans
WHERE
is_subscribe = 1
AND subscribe_date > 1535558400
AND subscribe_date < 1536163200
GROUP BY
date
ORDER BY
date ASC
时间字段日期时间格式：
# 统计 7 天每天的新增的积分和订单总金额
SELECT
DATE_FORMAT( time, '%Y-%m-%d' ) AS date,
COUNT( * ) AS count,
sum( add_points ) AS add_points,
sum( amount ) AS amount
FROM
`sys_order`
WHERE
( `time` > '2018-08-30' AND `time` < '2018-09-06' )
GROUP BY
date
ORDER BY
date ASC
其他使用示例
按小时：
SELECT
from_unixtime( create_time, "%Y%m%d%H" ) AS hours,
count( id ) AS counts
FROM
`table`
GROUP BY
hours;
按日：
SELECT
from_unixtime( create_time, "%Y-%m-%d" ) AS days,
count( id ) AS counts
FROM
`table`
GROUP BY
days;
按周：
SELECT
from_unixtime( create_time, "%Y-%u" ) AS weeks,
count( id ) AS counts
FROM
`table`
GROUP BY
weeks;
按月：
SELECT
from_unixtime( create_time, "%Y-%m" ) AS months,
count( id ) AS counts
FROM
`table`
GROUP BY
months;
按季度：
SELECT
concat( from_unixtime( create_time, "%Y" ), floor( ( from_unixtime( create_time, "%m" ) + 2 ) / 3 ) ) AS quarters,
count( id ) AS counts
FROM
`table`
GROUP BY
quarters;
按年：
SELECT
from_unixtime( create_time, "%Y" ) AS years,
count( id ) AS counts
FROM
`table`
GROUP BY
years;
补充
SELECT
FROM_UNIXTIME( create_time, '%Y-%m-%d' ) AS date,
COUNT(CASE when create_type=1 then 1 end ) as card,
COUNT(CASE when create_type=2 then 1 end ) as face,
COUNT(CASE when create_type=3 then 1 end ) as app,
COUNT(CASE when create_type=4 then 1 end ) as wx,
COUNT(CASE when create_type=5 then 1 end ) as anniu
FROM
txy_recordinout
WHERE
admin_id=9
AND create_time >= 1606752000
GROUP BY
date
image.png
