mysql日期格式化季度_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 位

参考链接: