文章目录

  • 前言
  • 1. DATE_ADD()与 DATE_SUB()
  • 1、 实例
  • 2. 获取日期间隔
  • 1、datediff(date1,date2)
  • 2、 timestampdiff(datetype,start_date,end_date)
  • 3、获取当月有多少天
  • 4、获取当前日期处于当年第几天
  • 3. 获取日期
  • 1、 以日为基准
  • 2、以周为基准
  • 3、以月为基准
  • 4、以季度为基准
  • 5、以年为基准
  • 4. date_format(date,format)
  • 5. 提取日期
  • 1、 判断时间范围为整年
  • 2、 年月日自由组合
  • 3、 字符串提取年月日
  • 3.1 substring
  • 3.2 left(data, length)
  • 3.3 right(data, length)


前言

本文主要记录关于SQL-日期操作的相关笔记

1. DATE_ADD()与 DATE_SUB()

查找某一天的后n个时间类型的用法:
DATE_ADD(date, INTERVAL n type)
查找某一天的前n个时间类型的用法。
DATE_SUB(date, INTERVAL n type)
  • date：起始时间，可以是年月日组成的日期，也可以是年月日时分秒组成给的日期时间。
  • n：指定起始日期的前n个时间类型或者后n个时间类型。
  • type：指定n的单位是什么时间类型。
    type的常用时间类型有：

1、 实例

统计一下record表中首次登录用户且次日也有登录成功记录的概率
这个概率=首次登录用户且次日也有登录成功记录个数/首次登陆记录个数

drop table if exists record;
CREATE TABLE record (
id int(4) NOT NULL,
date1 date NOT NULL,
PRIMARY KEY (id));
)

select round(count(distict id)*1.0/(select 
count(distinct id) from table),1) 
from record where (id,date1) in 
(select id,DATE_ADD(min(date1)，interval 1 day) 
from record group by id)
2. 获取日期间隔

1、datediff(date1,date2)

datediff(date1,date2)的功能是计算两个日期之间间隔的天数(date1-date2)
不过本函数只对年月日进行计算，不对时分秒进行计算，比如即便是下述时间相减结果的单位仍是天。

datediff('2021-01-01 23:59:59','2021-01-02 00:00:01')
结果：
-1

2、 timestampdiff(datetype,start_date,end_date)

timestampdiff(datetype,start_date,end_date)的功能是计算两个日期之间间隔的年、月、日、时、分、秒(end_date-start_date)。

timestampdiff(YEAR,'2021-01-01 23:59:59','2021-01-02 00:00:01')
timestampdiff(MONTH,'2021-01-01 23:59:59','2021-01-02 00:00:01')
timestampdiff(DAY,'2021-01-01 23:59:59','2021-01-02 00:00:01')
timestampdiff(HOUR,'2021-01-01 23:59:59','2021-01-02 00:00:01')
timestampdiff(MINUTE,'2021-01-01 23:59:59','2021-01-02 00:00:01')
timestampdiff(SECOND,'2021-01-01 23:59:59','2021-01-02 00:00:01')

3、获取当月有多少天

现有表table1中有time列，下面的操作将以time列的"2021-08-02 11:43:43"值为例

select dayofmonth(last_day(time)) from table1
输出：
31

4、获取当前日期处于当年第几天

现有表table1中有time列，下面的操作将以time列的"2021-08-02 11:43:43"值为例

select dayofyear(time) from table1
输出：
214
3. 获取日期

现有表table1中有time列，下面的各项操作都将以time列的"2021-08-02 11:43:43"值为例

1、 以日为基准

# 获取当前日期
select curdate() from table1
# 输出：2022-01-02

2、以周为基准

# 当日属于周几
select WEEKDAY(submit_time)+1 from table1
# 输出：1

# 当前日期所属周第一天的日期
select date_sub(time,interval WEEKDAY(time) day) from table1
# 输出：2021-08-01 11:43:43

3、以月为基准

# 取上个月第一天。
select date_sub(concat(date_format(time,'%Y-%m-'),'01'),interval 1 month) from table1
# 输出：2021-07-01


# 取当月最后一天。
select last_day(time) from table1
# 输出：2021-08-31

# 取当月第一天
# 方式1 拿当前日期的day-当月截至当日的天数再+1。
select DATE_sub(time,interval day(time)-1 day) from table1
select DATE_add(time,interval -day(time)+1 day) from table1
# 输出：2021-08-01 11:43:43
# 方式2 字符串连接
select concat(date_format(time,'%Y-%m-'),'01') from table1
# 输出：2021-08-01

# 取下个月第一天
select DATE_add(time-day(time)+1,interval 1 month) from table1
# 输出：2021-09-01 11:43:43

4、以季度为基准

# 获取当前季度
select QUARTER(time) from table1
# 输出：3

# 获取当前季度第一天
# 算出当前季度，然后将当年的1月1日加上（季度数*3-3）即可得当前季度的第一天。
select DATE_ADD(DATE_SUB(time,INTERVAL dayofyear(time)-1 DAY),INTERVAL QUARTER(time)*3-3 month) from table1
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM time),1) + interval QUARTER(time)*3-3 month),'%Y-%m-'),'01') from table1
# 输出：2021-07-01 11:43:43/2021-07-01

# 获取当前季度最后一天
# 算出当前季度，然后将当年的1月1日加上（季度数*3-1），在使用last_day即可得当前季度的最后一天。
select last_day(DATE_ADD(DATE_SUB(time,INTERVAL dayofyear(time)-1 DAY),INTERVAL QUARTER(time)*3-1 month)) from table1
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM time),1) + interval QUARTER(time)*3-1 month) from table1
# 输出：2021-09-30/2021-09-30

5、以年为基准

# 取当前年
select year(time) from table1
select extract(year from time) from table1
# 输出：2021

# 取当年的第一天的日期
# 方式1
select DATE_SUB(time,INTERVAL dayofyear(time)-1 DAY) from table1
# 输出：2021-01-01 11:43:43
# 方式2
select MAKEDATE(year(time),1) from table1
# 输出：2021-01-01

# 取当年的最后一天日期
select concat(YEAR(time),'-12-31') from table1
# 输出：2021-12-21
4. date_format(date,format)

date_format(date,format)根据format格式来输出结果。其中format格式可以选择如下：
说明中有单独例子的是以’2022-01-09 18:10:40’参考。

格式

说明

%Y

年,4位

%y

年,2位

%M

月名全称英文(eg: ‘January’)

%b

月名缩写英文(eg: ‘Jan’)

%m

月,数值(00-12)

%c

月,数值(1-12)

%d

日(月的天),数值(00-31)

%e

日(月的天),数值(0-31)

%H

时(00-23)

%k

时(0-23)

%h

时(01-12)

%l

时(1-12)(m字母前面的字母l)

%i

分,数值(00-59)

%S/s

秒(00-59)

%a

缩写星期名

%D

带有英文后缀的月中的天(eg: ‘9th’)

%f

微秒

%j

天(年的天)(001-366)

%p

AM或PM

%r

时间,12-小时(hh:mm:ss AM或PM)

%T

时间,24-小时(hh:mm:ss)

%U

周(00-53)(周日作为周的第一天)

%u

周(00-53)(周一作为周的第一天)

%W

星期名

%w

周的天(0=星期日,6=星期六)

%V

周(01-53)(周日作为周的第一天,与%X搭配使用)

%v

周(01-53)(周一作为周的第一天,与%x搭配使用)

%X

年(周日作为周的第一天,4位,与%V搭配使用)

%x

年(周一作为周的第一天,4位,与%v搭配使用)

下面举个具体的例子：
数据库中日期时间为：‘2022-01-09 18:10:40’

SELECT date_format(date,'%b %d %Y %h:%i %p') FROM table1
结果：
'Jan 09 2022 06:10 PM'
5. 提取日期

1、 判断时间范围为整年

这里以判断时间范围为整个2022年。

方式1：
date>='2022-01-01' and date<'2023-01-01'
方式2：
between '2022-01-01' and '2022-12-31'
方式3：
year(date)=2022

2、 年月日自由组合

%Y为年，%m为月，%d为日，可以组合使用，年月日之间的连接符可以使用如下代码块中的“-”号，或者可以自己设定其他的。

按年月
date_format(date,'%Y-%m')
按月
date_format(date,'%m')
按日
date_format(date,'%d')

3、 字符串提取年月日

当原始date为“2022-01-01”之类的数据格式时，进行提取

3.1 substring

substring(date,start,length)
  • date——要提取所用的完整日期
  • start——整数或可以隐式转换为int 的表达式，指定子字符串的开始位置。
  • length——整数或可以隐式转换为 int的表达式，指定要提取子字符串的长度
    eg：
提取年
substring(date,1,4)
提取月
substring(date,6,2)
提取日
substring(date,9,2)

3.2 left(data, length)

取date的前length个字符。

提取年
left(date,4)
提取年月
left(date,7)

3.3 right(data, length)

取date的后length个字符。

提取日
right(date,2)
提取月日
right(date,5)