mysql基础9——时间函数

统计一天中每小时的销售数量和销售金额

将数据按照小时进行分组统计 关键就是把交易时间的小时部分提取出来 使用extract()和hour()

使用extract()获取小时的值

extract(type from date)    表示从日期时间数据date中抽取type指定的部分

select extract(hour from b.transdate) as 时段,sum(a.quantity) as 数量,sum(a.salesvalue) as 金额
from demo.transactiondetails as a
join demo.transactionhead as b on (a.transactionid=b.transactionid)
group by extract(hour from b.transdate)
order by extract(hour from b.transdate);

查询过程：

1）从交易时间中抽取小时信息

2）按交易的小时信息分组

3）按分组统计销售数量和销售金额的和

4）按交易的小时信息排序

使用hour(date时间)获取小时信息也可

select hour(b.transdate) as 时段,sum(a.quantity) as 数量,sum(a.salesvalue) as 金额
from demo.transactiondetails as a
join demo.transactionhead as b on (a.transactionid=b.transactionid)
group by hour(b.transdate)
order by hour(b.transdate);

计算日期时间的函数

date_add(date, interval表达式 type)

表示计算从时间点’date‘开始，向前或者向后一段时间间隔的时间 ；

表达式的值为时间间隔数 正数表示向后，负数表示向前 ；

type表示时间间隔的单位（月,日,年等）；

last_day(date)

表示获取日期时间“date”所在月份的最后一天的日期

假设今天是2020年12月10日，计算这个月单品销售金额的统计 以及去年同期相比的增长率

关键点是获取2019年12月10的销售数据 --> 计算2019年12月的起始和截至时间点就是关键

1）使用date_add函数获取到2020年12月10日上一年的日期：2019年12月10日

select date_add('2020-12-10',interval -1 year);

2）获取2019年12月10日这个时间节点开始上个月的日期 

select date_add(date_add('2020-12-10',interval -1 year), interval -1 month);

3）获取2019年11月10日这个时间点月份的最后一天

select last_day(data_add(date_add('2020-12-10',interval -1 year),interval -1 month));

4）计算2019年11月30日后一天的日期  12月01日

select date_add(last_day(data_add(date_add('2020-12-10',interval -1 year),interval -1 month)),interval +1 day);

5）获取截至日期

select date_add(last_day(data_add('2020-12-10',interval -1 year)),interval +1 day));

每周的不同天都会打不同的折扣，知道某天具体的价格，需要知道当前的日期以及今天是星期几 

curdate()

获取当前的日期  日期格式为“YYYY-MM-DD” 年月日的格式

dayofweek(date) 

获取日期date是周几 1表示周日，2表示周一，...，7表示周六

假设今天是2021年2月6日，查询今天商品的全部折扣后的价格

select
curdate() as 日期，
case dayofweek(curdate()) -1 when 0 then 7 else dayofweek(curdate())-1
a.goodsname as 商品价格
a.salesprice as 价格
ifnull(b.discountrate,1) as 折扣率
a.salesprice * ifnull(b.discountrate,1) as 折后价格
from demo.goodmaster as a
left join demo.discountruble as b on (a.itemnum=b.itemnum) 
and case dayofweek(curdate()) -1 when 0 then 7 else dayofweek(curdate())

case函数语法

case 表达式 when 值1 then 表达式1 [when 值2 then 表达式2] else 表达式m end

date_format  将日期按照指定格式显示

24小时制查看时间  

select date_format("2020-12-01 13:25:50", "%T");

上下午方式查看时间

select date_format("2020-12-01 13:25:50", "%r");

获取今天是星期几

datediff(date1,date2)

表示日期“date1”与“date2”之间相隔几天

计算某段时间的每天交易金额的平均值，把起始日期和截至日期之间的总金额，再除以天数

select datediff("2021-02-01","2020-12-01");

注意：mysql获取的时间就是mysql服务器计算机的系统时间  若系统有一定规模，需在多台计算机上运行，注意时间较准问题，

解决方法

1）利用windows系统自带的网络同步方式 较准系统时间

2）统一从总部myssql服务器获取时间（或采用云服务器，系统时间的可靠性更高）