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 周末
- 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
