按月查询
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 周末
- 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