mysql 按月,周 查询,mysql日期函数

按月查询

select from_unixtime(start_time,'%Y-%m') as 'yue',count('yue') as shuliang from br_collect  GROUP BY yue

1.select from_unixtime(‘1491408000’,’%Y-%m’)
格式为标准时间

2. select unix_timestamp(‘2017-03-01’) as time_unix
转换成unix时间戳

3. SELECT DATE_FORMAT(‘2017-04-05’,’%Y-%m-%d’)

按周查询

select subdate(date_format(now(),'%Y-%m-%d'),date_format(now(),'%w')-1) as 周一,subdate(date_format(now(),'%Y-%m-%d'),date_format(now(),'%w')-7) as 周末

1. subdate(‘2017-04-20’,date_format(now(),’%w’)-1)
   获取指定日期 周第一天

select subdate(date_format(now(),'%Y-%m-%d'),date_format(now(),'%w')-1) as 本周一

2
start_time :为数据库时间戳

select from_unixtime(start_time,'%v周') as 'zhou',subdate(from_unixtime(start_time,'%Y-%m-%d'),from_unixtime(start_time,'%w')-1) as 周一,subdate(from_unixtime(start_time,'%Y-%m-%d'),from_unixtime(start_time,'%w')-7) as 周末,count('*') as 总授权量 from br_collect GROUP BY zhou