有趣的SQL时间函数

-- 001 NOW(),获取当前日期和时间的函数。
SELECT NOW()

-- 002 CURDATE,获取当前的日期
SELECT CURDATE()

-- 003 CURTIME(),获取当前时间
SELECT CURTIME();

-- 004 DATA() 获取日期时间或者日期的日期部分
SELECT DATE('2020-04-01 00:11:06')
SELECT DATE('2020-04-01')
SELECT date(now());

-- 005 EXTRACT,获取返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
-- 语法：EXTRACT(unit FROM date)
-- 注意：取出单独部分

 获取当前年月
select EXTRACT(YEAR_MONTH FROM now());
select EXTRACT(DAY_HOUR FROM now()); 
select EXTRACT(DAY_MINUTE FROM now()) AS a;
select EXTRACT(DAY_SECOND FROM now()) AS a;


获取当前周
SELECT EXTRACT(WEEK FROM NOW())

获取当前月份
SELECT EXTRACT(MONTH FROM NOW())
SELECT EXTRACT(MONTH FROM '2020-03-01 00:11:06') #探索式，结果成立
SELECT EXTRACT(MONTH FROM '202004') #探索式，结果为空，无效值 ①
SELECT EXTRACT(MONTH FROM '20200401') #探索式成功 ②
/*
由①②得，日期格式完整性很重要！！！
*/


获取当前季度
SELECT EXTRACT(QUARTER FROM NOW())

获取当前日期 + 时间
SELECT EXTRACT(MINUTE_SECOND FROM NOW()) # 理解上有些唐突
SELECT EXTRACT(MINUTE FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());


-- 006 将日期添加指定的时间间隔。
-- 语法：DATE_ADD(date,INTERVAL expr type)

获取两周后的时间
select DATE_ADD(now(),INTERVAL 2 WEEK);

-- 007 DATE_SUB：将日期减去指定的时间间隔。
-- 语法：DATE_SUB(date,INTERVAL expr type)

 获取两天前的时间
SELECT DATE_SUB(NOW(),INTERVAL 2 DAY); --负负得正!!!
SELECT DATE_SUB(NOW(),INTERVAL -2 DAY);
-- OR
select DATE_ADD(now(),INTERVAL -2 DAY);


-- 008 DATEDIFF
-- 获取两个日期之间的天数。（只有日期部分参与计算）
-- 语法：DATEDIFF(date1,date2)
-- date1 和 date2 参数是合法的日期或日期/时间表达式。(何谓合法？)

date1 和 date2 参数是合法的日期或日期/时间表达式。
SELECT DATEDIFF(NOW(),'2008-08-08') AS '北京奥运会多天前'
SELECT DATEDIFF(NOW(),'20080808') AS '北京奥运会多天前' # 探索结果成立
SELECT DATEDIFF('20080808',NOW()); -- 最近的日期放前面,否则出现负值;


-- 009 DATE_FORMAT

-- 格式化日期时间
-- 语法：DATE_FORMAT(date,format)
-- date 参数是合法的日期。format 规定日期/时间的输出格式。

-- 年
SELECT DATE_FORMAT('2019-06-08','%Y') # 返回年,4位
SELECT DATE_FORMAT('2019-06-08','%y') -- 年,2位
SELECT DATE_FORMAT('20190608','%Y') # 探索OK


-- 以一周为单位
SELECT DATE_FORMAT(now(),'%w') # 返回周的天(0 = 星期日, 6 = 星期六)
SELECT DATE_FORMAT(now(),'%W') # 返回星期名

-- 以年为单位,表示一年的多少周

-- %V：周 (01-53) 星期日是一周的第一天，与 %X 使用
SELECT DATE_FORMAT(now(),'%V')
SELECT DATE_FORMAT('20200419','%V')
SELECT DATE_FORMAT('20200418','%V')

-- %v：周 (01-53) 星期一是一周的第一天，与 %x 使用
SELECT DATE_FORMAT('20200419','%v')

-- 更多请自主探索;建议根据业务需要做中学;


/*
作者：@zhangjunhong
一文搞定Mysql日期时间函数 
https://mp.weixin.qq.com/s/0Y_AjWjgn5KjisWP22RTxg
*/

/*
1.获取当前时刻时间
    1.1返回当前时刻的日期和时间
    1.2获取当前时刻的日期
    1.3获取当前时刻的时间
    1.4获取当前时刻的周数
2.日期时间格式转换
3.日期时间运算
    3.1向后偏移时间
    3.2向前偏移时间
    3.3两日期做差
*/
-- 1.获取当前时刻时间
-- 1.1返回当前时刻的日期和时间
select now() -- 当前时间为：2020-04-19 21:31:07

-- 1.2获取当前时刻的日期
SELECT curdate(); -- 获取当前时刻的日期部分

/*
curdate()函数是直接获取当前时刻的日期部分，
我们也可以先通过now()函数获取当前时刻的日期时间，
然后再通过date()函数将日期时间转化为日期部分
*/

SELECT date(now()); -- 目的同上;
SELECT YEAR(now()); -- 获取日期中的年;
SELECT MONTH(now()); -- 获取日期中的月;
SELECT DAY(now()); -- 只获取日期中的日;


-- 1.3获取当前时刻的时间
SELECT curtime(); -- 获取当前时刻的时间部分

/*
也可以先通过now()函数获取当前时刻的日期时间，
然后再通过time()函数将日期时间转化为时间部分，具体代码如下：
*/
SELECT time(now()); -- time()函数将日期时间转化为时间部分
SELECT HOUR(now()); -- 获取时间中的小时;
SELECT minute(now()); -- 获取时间中的分钟;
SELECT SECOND(now()); -- 获取时间中的秒;

-- 1.4获取当前时刻的周数
SELECT WEEKOFYEAR(now());-- 全年的第几周;

SELECT DAYOFWEEK(now());-- 一周内的周几:周天对应的是1

-- 2.日期时间格式转换
select date_format("2019-01-01 22:47:37","%y-%m-%d") -- %Y 对应4位数的年,%y 对应2位数的年 
select date_format("2019-01-01 22:47:37","%Y-%m-%d") -- %m 01-12的月
select date_format("2019-01-01 22:47:37","%Y-%c-%d") -- %c 1-12的月
select date_format("2019-01-01 22:47:37","%Y-%M-%d") -- %M 月份对应的英文全称	
select date_format("2019-01-01 22:47:37","%Y-%b-%d") -- %M 月份对应的英文缩写	

select date_format("2019-12-25 22:47:37","%H:%i:%S") -- %H 对应00-23小时
select date_format("2019-12-25 22:47:37","%T") -- %T 返回当前的时分秒 24-小时(hh:mm:ss)

select date_format("2019-12-25 22:47:37","%h:%i:%S") -- %h 对应01-12小时
select date_format("2019-12-25 22:47:37","%h") -- 仅返回日期时间中的"小时"数;

-- extract 用于返回具体日期时间中的单独部分，比如年、月、日、小时、分钟等等
-- 具体形式如下：
extract(unit from datetime)

select
    extract(year from "2019-12-25 22:47:37") as col1
    ,extract(month from "2019-12-25 22:47:37") as col2
	,extract(week from "2019-12-25 22:47:37") as col3
    ,extract(day from "2019-12-25 22:47:37")  as col4
	,extract(hour from "2019-12-25 22:47:37")  as col5
	,extract(minute from "2019-12-25 22:47:37")  as col6
	,extract(minute from "2019-12-25 22:47:37")  as col7
	,extract(second from "2019-12-25 22:47:37")  as col8;
		
-- 3.日期时间运算
/*
有的时候我们也需要对日期之间进行运算，
比如我要获取今天往前7天对应的日期，
或者今天往后13天对应的日期，可以去翻日历，也可以去数数，
但是这些方法肯定都不是最直接的方法。
所以需要日期之间的运算。
*/

-- 3.1向后偏移时间
date_add(date,interval x unit) -- date表示当前的日期，或者当前的日期时间；interval是一个固定的参数；

select 
    "2019-01-01" as col1 -- 初始化时间
    ,date_add("2019-01-01",interval 7 year) as col2
    ,date_add("2019-01-01",interval 7 month) as col3
    ,date_add("2019-01-01",interval 7 day) as col4


select 
    "2019-01-01 01:01:01" as col1
    ,date_add("2019-01-01 01:01:01",interval 7 hour) as col2
    ,date_add("2019-01-01 01:01:01",interval 7 minute) as col3
    ,date_add("2019-01-01 01:01:01",interval 7 second) as col4;


-- 3.2向前偏移时间 
-- date_sub()函数
select 
    "2019-01-01" as col1
    ,date_sub("2019-01-01",interval 7 year) as col2
    ,date_sub("2019-01-01",interval 7 month) as col3
    ,date_sub("2019-01-01",interval 7 day) as col4

-- 也可继续使用date_add()函数
-- 通过运行上面的结果与使用date_sub得出来的结果是一致的。
select 
    "2019-01-01" as col1
    ,date_add("2019-01-01",interval -7 year) as col2
    ,date_add("2019-01-01",interval -7 month) as col3
    ,date_add("2019-01-01",interval -7 day) as col4

-- 3.3两日期/时间做差
-- 大的日期/时间需要在第一个参数的位置,则返回正数,否则返回负数;
select datediff("2019-01-07","2019-01-01")
select datediff("2019-01-01","2019-01-07") -- 为负值,最近日期放在前面较妥当;

SELECT timediff('23:23:49','23:09:49');--返回的是时分秒的格式;

-- hayangteacher-常用时间函数

SELECT DAYOFWEEK('2020-04-19 23:09:49');-- 返回date所代表的一星期中的第几天(1~7),1是星期天,7是星期六;
SELECT weekday('2020-04-19') -- 返回date所代表的一星期中的第几天(0~6),0是周一,6是星期天;
SELECT DAYOFMONTH('2020-04-19 23:09:49'); -- 返回date是一年中的第几天(1~31)
SELECT DAYOFYEAR('2020-04-19 23:09:49');-- 返回date是一年的第几天(1~366)
SELECT DAYNAME('20200419') -- 返回date的星期名
SELECT DAYNAME('2020-04-19 23:09:49') -- 返回date的星期名
SELECT MONTH('20200419') -- 返回统计的月份(1-4)
SELECT MONTHNAME('20200419') -- 返回月份名
SELECT WEEK('20200419') -- 返回日期date为一年中的第几周(0~53)
SELECT QUARTER('20200419') -- 返回date所在季度(1~4)
SELECT HOUR('2020-04-19 23:09:49') -- 返回time的小时值(0-23)
SELECT MINUTE('2020-04-19 23:09:49') -- 返回time的分钟值(0-59)

-- 把字符类型转换为时间类型：STR_TO_DATE(str,format)

SELECT str_to_date('2020-04-09','%Y-%m-%d')
SELECT '2020-04-09'; -- 暂且到此

更多阅读文献研讨：

https://www.cnblogs.com/ggjucheng/p/3352280.html

https://baijiahao.baidu.com/s?id=1608326786755050044&wfr=spider&for=pc

https://mp.weixin.qq.com/s/0Y_AjWjgn5KjisWP22RTxg

附图1：

date_format(datetime,format)
datetime表示要被转换的具体的日期时间，format表示要转换成的格式，可选的格式

附图2：

extract(unit from datetime)
datetime表示具体的日期时间，unit表示要从datetime中返回的单独的部分。unit值可以是下列的值：