时间函数，SQL获取，实际案例，简单易懂

时间函数：

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)是(  在一个关系中的一个或一组属性是另一个关系的主码 )。