mysql 日期函数 按天,按周,按月,按年分组 统计数据
在工作业务中常会使用mysql的日期函数,下面做一下关于这方面的sql整理:
1.内置日期函数
- now(): 返回当前的日期和时间
- unix_timestamp(): 返回自
1970-01-01 00:00:00到当前时间的秒数差(时间戳) - unix_timestamp(指定时间): 返回自
1970-01-01 00:00:00到指定时间的秒数差(时间戳) - from_unixtim(时间字段): 将unix(时间戳)转为datetime日期
- date_format(时间字段, format): 以不同的格式显示日期或时间数据
补充: 字符串截取函数
- left('example.com', 3):
exa, 从最左边开始截取 - right('example.com', 3):
com, 从最右边开始截取 - substring('example.com', 4):
mple.com,从第4个字符位置开始取,索引为1开始 - substring('example.com', 4, 2):
mp,从第四个字符位置开始取2个字符 - substring('example.com', -4):
.com,从右边开始数第4个字符位置开始取 - substring('example.com', -4, 2):
.c,从右边开始数第4个字符位置开始取2个 - substring_index('www.example.com', '.', 2):
www.example, 截取第二个 '.' 之前的所有字符 - substring_index('www.example.com', '.', -2):
example.com, 截取倒数第二个 '.' 之后的所有字符 - substring_index('www.example.com', '.coc', 1):
www.example.com, 没有查找到指定的值就返回整个字符串
2.查询每日,每周,每月,每季度,每年相关的sql语句
查询2019年每天有多少条记录:
select date_format(时间字段,'%Y-%m-%d') days,count(*) from 表名 where date_format(时间字段,'%Y')='2019' group by days
--或
select count(*),DATE(时间字段名) from 表名 where YEAR(时间字段名)='2019' group by DAY(时间字段名)
查询8月份每周有多少条记录:
select date_format(时间字段,'%Y%u') weeks,count(*) from 表名 where date_format(时间字段,'%m')='08' group by weeks
--或
select count(*),WEEK(时间字段名) from 表名 where MONTH(时间字段名)='8' group by WEEK(时间字段名)
查询2019年周一到周五每天总共有多少条记录:
select count(*),DAYNAME(时间字段名) from 表名 where YEAR(时间字段名)='2019' group by DAYNAME(时间字段名)
查询本周有多少条记录:
select count(*) from 表名 where MONTH(时间字段名) = MONTH(CURDATE()) and WEEK(时间字段名) = WEEK(CURDATE())
查询2019年每月有多少条记录:
select date_format(时间字段,'%Y%m') months,count(*) from 表名 where date_format(时间字段,'%Y')='2019' group by months
--或
select count(*),MONTH(时间字段名) from 表名 where YEAR(时间字段名)='2019' group by MONTH(时间字段名)
查询本月有多少条记录:
select count(*) from 表名 where MONTH(时间字段名)=MONTH(CURDATE()) and YEAR(时间字段名) = YEAR(CURDATE())
查询2019年每季度有多少条记录:
select count(*), concat(year(时间字段),floor((date_format(时间字段, '%m')+2)/3)) quarters from 表名 group by quarters
--或
select count(*),QUARTER(时间字段名) from 表名 where YEAR(时间字段名)='2019' group by QUARTER(时间字段名)
查询每年有多少条记录:
select date_format(时间字段, '%Y') years,count(*) from 表名 group by years
--或
select count(*),YEAR(时间字段名) from 表名 group by YEAR(时间字段名)
3.时间段查询
查询当天的所有记录:
select * from 表名 where date(时间字段名) = date(now())
--或
select * from 表名 where to_days(时间字段) = to_days(now())
查询n天内所有记录:
select * from 表名 where to_days(now()) - to_days(时间字段名) <= n
查询一周内的所有记录:
select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)
查询一个月内的所有记录:
select * from 表名 where date_sub(curdate(), interval 1 month) <= date(时间字段名)
查询'08-06'到'12-12'时间端内所有记录:
select * from 表名 where date_format(时间字段名,'%m-%d') >= '08-06' and date_format(时间字段名,'%m-%d') <= '12-12'
4.date_format()中format可以使用的格式
- 格式 描述
- %a 缩写星期名
- %b 缩写月名
- %c 月，数值
- %D 带有英文前缀的月中的天
- %d 月的天，数值（00-31）
- %e 月的天，数值（0-31）
- %f 微秒
- %H 小时（00-23）
- %h 小时（01-12）
- %I 小时（01-12）
- %i 分钟，数值（00-59）
- %j 年的天（001-366）
- %k 小时（0-23）
- %l 小时（1-12）
- %M 月名
- %m 月，数值（00-12）
- %p AM 或 PM
- %r 时间，12-小时（hh:mm:ss AM 或 PM）
- %S 秒（00-59）
- %s 秒（00-59）
- %T 时间, 24-小时（hh:mm:ss）
- %U 周（00-53）星期日是一周的第一天
- %u 周（00-53）星期一是一周的第一天
- %V 周（01-53）星期日是一周的第一天，与 %X 使用
- %v 周（01-53）星期一是一周的第一天，与 %x 使用
- %W 星期名
- %w 周的天（0=星期日, 6=星期六）
- %X 年，其中的星期日是周的第一天，4 位，与 %V 使用
- %x 年，其中的星期一是周的第一天，4 位，与 %v 使用
- %Y 年，4 位
- %y 年，2 位
参考链接:
