时间函数:
TODAY(): 返回当前日期
DATE():返回表示特定日期的连续序列号
Time:返回指定时间的序列号。
=Time("时","分","秒")
EOMONTH():以日期/时间格式返回指定月份数之前或之后的月份的最后一天的日期
EOMONTH(, )
start_date | 日期/时间格式的开始日期,或日期的可接受文本表示形式 。 |
months | 表示 start_date 之前或之后的月份数的数字 。 注意: 如果输入的数字不是整数,则该数字将向上或向下舍入到最接近的整数。 |
常规日期提取与转换函数:**
当前日期:
- now()
、 current_timestamp()、localtime()、sysdate()、localtimestamp() --- 返回当前系统详细时间的
- curdate()
、current_date() --- 返回系统当前年月日
- curtime()
、current_time() --- 返回当前小时分钟秒数
提取相关:
- year(d):提取年份
- month(d):提取月份
- day(d):提取日
- quarter(d):返回日期d是第几个季度
- hour(t):提取小时数
- minute(t):提取分钟数
- second(t):提取秒数
转换返回:
- dayname(d):返回该日期为星期几
- dayofweek(d):返回该日期是一周的第几天,周日为1
- week(d):返回该日期为本年的第几个星期
- dayofyear(d):返回该日期是本年的第几天
- dayofmonth(d):返回该日期是本月的第几天
- extract(type from d) : 从日期中获取指定的值
- type指定返回的值 ---year 、hour 、month等时间提取
time_to_sec(t):将当天的时间t 转换为秒数
- date() :直接将字段转换类型为 年月日格式
- date_format,日期格式化函数,时间的格式转换
- str_to_date:日期格式化函数,字符串转换为date
日期计算相关:
- datediff(结束日期,起始日期):计算日期d1~d2之间的相隔天数
- timestampdiff(type,d1,d2):计算日期d1~d2之间的相隔的单位差(type决定单位:year、month。。。)
- adddate(d,n):计算起始日期d加上n天的日期
- adddate(d,interval expr type):计算起始日期d加上一个时间段后的日期,interval关键字,后面跟一段时间
- expr:长度 1、2
- type:单位 year hour等
- subdate(d,n):计算起始日期d减去n天的日期
- subdate(d,interval expr type):计算起始日期d减去一个时间段后的日期,interval关键字,后面跟一段时间
- 查询出本日是寿星的员工,输出 姓名,部门,生日 --- northwind 员工**
- select 姓名,部门,出生日期from 员工where month(出生日期) = month(now()) and day(出生日期) =day(now())
- select 姓名,部门,出生日期from 员工where date_format(出生日期,'%m-%d') = date_format(now(),'%m-%d')
日期函数的进阶:
获取本月的第一天
思路: 1)利用日期格式化函数 2)利用推移函数,获取当前日期值后利用adddate往前推该日值+1,或使用subdate计算起始日期减去该日后的日期
#方式一
select date_format(curdate(),"%Y-%m-01") as 本月的第一天
#方式二
subdate 往前推算,其数字(日的数字) 由当天的日值 -1 即可
select subdate(curdate(),interval day(curdate())-1 day)
#方式三
adddate 往前推算,其数字(日的数字) 由当天的日值的负数形式 +1 即可
select adddate(curdate(),interval -day(curdate())+1 day)
本月的最后一天日期
#方式一:直接用last_day() select last_day(curdate()) 本月的最后一天 #方式二:先求下个月的第一天日期[可先获取本月第一天后,往后推算1个月],再往前推一天就是本月的最后一天日期 select subdate(adddate(adddate(curdate(),interval -day(curdate())+1 day) ,interval 1 month),interval 1 day) #简化方式二的操作,date(curdate()-day(curdate())+1)--- 获取到本月的第一天 # 其中date() 为日期格式转换函数,由于adddate等其参数最好是个日期格式,且推移后的结果也会是个日期格式,否则就是文本内容 select subdate(adddate(date(curdate()-day(curdate())+1), interval 1 month),interval 1 day)
获取上个月的最后一天
#方式一:先求本月的第一天,再往前推一天 select subdate(date(curdate()-day(curdate())+1),1) #方式二:拿当前的日期减去当前的天数 select subdate(curdate() ,interval day(curdate()) day) #方式三:直接使用last_day(上月的某个日期) select last_day(subdate(curdate(),interval 1 month))
5)求下月的第一天日期和下月最后一天的日期
# 5.1 下个月的第一天 #1)date_format 完成 select date_format(adddate(curdate(),interval 1 month),"%Y-%m-01") 下月的第一天 #2) 先求本月的第一天日期,往后推1一个月 select adddate(date(curdate()-day(curdate())+1),interval 1 month) 下月的第一天 #5.2 下个月的最后一天 #1)先求下下个月的第一天,往前推1天 select subdate(adddate(date(curdate()-day(curdate())+1),interval 2 month),1) 下个月的最后一天 #2)直接使用last_day(下月的某个日期) select last_day(adddate(curdate(),interval 1 month))
6)获取当前月的天数
思路:利用下个月的第一天与本月的第一天 日期进行间隔计算
select datediff(adddate(date(curdate()-day(curdate())+1),interval 1 month), adddate(curdate(),interval -day(curdate())+1 day) )
关于周的相关数据写法
1)weekday() 获取今天是一周的第几天 --- 周1 为 0 ,周日为 6
2)dayofweek() 获取今天是一周的第几天 --- 周日为1
3)yearweek(date,[mode]) 用于查找给定日期的年和周
- date:我们要从中提取年和周的日期或日期时间。
- mode:它指定一周中的哪一天开始
weekofyear() 、week()返回某一天是那一年的第几周
获取本周的第一天日期(周一)
获取本周的第一天日期(周一)
# 方式一:获取当前日期为一周内的第几天后(weekday进行),将其往前推算 select subdate(curdate(),interval weekday(now()) day) #方式一:获取当前日期为一周内的第几天后(dayofweek进行),将其往前推算 select subdate(curdate(),interval dayofweek(now())-2 day)
-- 计算每年每个月下的每一周的订单总数,效果如下--northwind 订货主档
select year(订单日期) as 年,month(订单日期) as 月,weekofyear(订单日期) as 周别,count(*) as 订单数 from 订货主档
group by year(订单日期),month(订单日期),weekofyear(订单日期) order by year(订单日期),month(订单日期),weekofyear(订单日期);
统计每年每月每周的周销售总量,将周别显示为该月的第几周,效果如下--- northwind 订货
主档
提示:
1、可配合窗口函数,按年、月维度进行分组的累计统计 count() over()
2、第几周显示方式:concat拼接
1.select t.年,t.月,count(*) over(partition by t.年,t.月 order by 周别 asc) as 第几周,t.订单数 from(
select year(订单日期) as 年,month(订单日期) as 月,weekofyear(订单日期) as 周别,count(*) as 订单数 from 订货主档
group by year(订单日期),month(订单日期),weekofyear(订单日期) order by year(订单日期),month(订单日期),weekofyear(订单日期)) t;
2.select t.年,t.月,row_number() over(partition by t.年,t.月 order by 周别 asc) as 第几周,t.订单数 from(
select year(订单日期) as 年,month(订单日期) as 月,weekofyear(订单日期) as 周别,count(*) as 订单数 from 订货主档
group by year(订单日期),month(订单日期),weekofyear(订单日期) order by year(订单日期),month(订单日期),weekofyear(订单日期)) t;
第几周的具体显示方式:concat拼接即可
concat("第",row_number() over(partition by t.年,t.月 order by t.周别 asc),"周") 第几周
要求统计出每一年月周冠军,要求显示效果--- northwind 订货主档
select t2.年,t2.月,group_concat(t2.第几周) as 周冠军,t2.排名,t2.周下单数
from (
#2、对该月再当年的第几周的值进行行累计获取其累计值【相当于行编号】,及排名
select t.年,t.月,
concat("第",row_number() over(partition by t.年,t.月 order by t.周别 asc),"周")
第几周
,t. 周下单数,
rank() over (partition by t.年,t.月 order by t. 周下单数 ) as 排名
from(
#1)获取每年每月下的每一周的订单数,得到年份、月份、该月再当年的第几周、订单数
select year(订单日期) as 年,month(订单日期) 月,
weekofyear(订单日期) 周别,count(*) as 周下单数
from 订货主档
group by year(订单日期),month(订单日期),
weekofyear(订单日期)
order by year(订单日期),month(订单日期) ,
weekofyear(订单日期)
) t
) t2 where t2.排名=1
group by t2.年,t2.月,t2.周下单数;
MySQL CURDATE() 函数
定义和用法
CURDATE() 返回当前的日期。
实例
下面是 SELECT 语句:
SELECT NOW(),CURDATE(),CURTIME()
结果如下所示:
NOW() | CURDATE() | CURTIME() |
2008-11-11 12:45:34 | 2008-11-11 | 12:45:34 |
现在有一张借款表 loan_table,这张表包含id、loan_time(借款时间)、expire_time(到期时间)、reback_time(还款时间)、amount(金额)和status(还款状态,1表示已还款、0表示未还款)六个字段,现我们想获取每天到期的借款笔数、借款金额和平均借款天数,该怎么实现呢?
select count(id) as 到期借款笔数, sum(amount) 到期总金额, avg(datediff(reback_time,loan_time)) as 平均借款天数 from loan_table where expire_time ='2020/1/9'
基于loan_table数据表,想继续知道有多少笔借款会在未来7天内到期,以及其中有多少笔是已经还款的
思路:
1)先筛选得到最近7天内的到期的数据
2)在通过还款状态 status(还款状态,1表示已还款、0表示未还款) 进行判断,统计已经还款的笔数
select count(id) as 即将到期借款笔数, count(if(status=1,id,null)) 已还款的笔数 from loan_table #1、获取当天及之后7天的数据内容,利用adddate或date_add 进行天数推移,推算的时候其函数会把当天算进去 where expire_time between curdate() and adddate(curdate(),interval 6 day)
关系数据库中,外码(ForeignKey)是( 在一个关系中的一个或一组属性是另一个关系的主码 )。