1 日期格式化

MySQL 使用 DATE_FORMAT()
格式化日期，和格式化相关的函数还有 STR_TO_DATE()
、TIME_FORMAT()
。

1.1 DATE_FORMAT(date,format)

DATE_FORMAT
根据 format
串格式化 date
值，每个格式符前必须包含 %
。

下表列出经常用到的格式：

日期格式化的例子

#输出格式如：2020-01-23 00:24:12
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T')

#输出格式如：20200123
SELECT DATE_FORMAT(NOW(),'%Y%m%d')

#本月第一天，如：2020-01-01
SELECT DATE_FORMAT(NOW(),'%Y-%m-01')

1.2 TIME_FORMAT(time,format)

TIME_FORMAT()
用法与 DATE_FORMAT()
函数类似，但是格式字符串可能仅包含小时，分钟，秒和微秒的格式说明符。其他说明符产生 NULL 值或 0。

如果时间值中的小时部分大于23，则 ％H
和 ％k
小时格式说明符会产生一个比通常范围 0..23 大的值。其它小时格式说明符会产生模 12 的小时值。

SELECT TIME_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
SELECT TIME_FORMAT('25:12:23', '%H %k %h %I %l');
-------------------------------------------------
0000-00-00 11:22:08
25 25 01 01 1

1.3 STR_TO_DATE(str,format)

将字符串转换成日期或者时间，或者日期时间，取决于给定的 format
包含了哪部分内容。如果 format
包含了日期和时间格式，将返回 datetime
类型的值；如果只包含日期格式，则返回 date
类型的值；如果只包含了时间格式，将返回 time
类型的值。

SELECT STR_TO_DATE('01/23/2020','%m/%d/%Y');
SELECT STR_TO_DATE('Jan 23,2020','%M %d,%Y');
SELECT STR_TO_DATE('23,01,2020 00:28:12','%d,%m,%Y %h:%i:%s');
# 未指定日期或者时间部分的值为 0
SELECT STR_TO_DATE('abc','abc');
--------------------------------------------------------------
2020-01-23
2020-01-23
2020-01-23 00:28:12
0000-00-00

1.4 GET_FORMAT()

GET_FORMAT()
有两个参数，第一个参数是DATE
、TIME
、DATETIME
、TIMESTAMP
中的一种，第二参数可以是EUR
、USA
、JIS
、ISO
、INTERNAL
等值。

该函数返回日期/时间的格式字符串，与 DATE_FORMAT()
和 STR_TO_DATE()
函数结合使用时非常有帮助。

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'ISO'));
SELECT STR_TO_DATE('2020-01-28 16:33:00',GET_FORMAT(TIMESTAMP,'ISO'));
----------------------------------------------------------------------
2020-01-28
2020-01-28 16:33:00

2 日期计算

2.1 DATE_ADD(date,INTERVAL expr unit)

date_add()
可用于日期、时间的加减计算，类似的函数还有 date_sub()
、adddate()
、addtime()
、subdate()
、subtime()
。

date_add()
接收两个参数，第一个参数可以是 date 类型或者 datetime 类型，第二个参数是个间隔值，表示将在第一个参数的基础上增加或者减少某个单位时间的值。

SELECT DATE_ADD('2020-01-28',INTERVAL 1 DAY);
SELECT DATE_ADD('2020-01-28',INTERVAL -1 YEAR);
SELECT DATE_ADD('2020-01-28 15:28:01',INTERVAL 2 HOUR);
SELECT DATE_ADD('2020-01-28 15:28:01',INTERVAL '1 2' DAY_HOUR);
---------------------------------------------------------------
2020-01-29
2019-01-28
2020-01-28 17:28:01
2020-01-29 17:28:01

2.2 DATEDIFF(expr1,expr2)

DATEDIFF()
函数用于计算两个日期之间相差的天数，计算方式是 expr1 - expr2。expr1 和 expr2 是日期或日期时间表达式，在计算中仅使用值的日期部分。

SELECT DATEDIFF('2020-01-28','2020-01-27');
SELECT DATEDIFF('2020-01-28 00:00:00','2020-01-27 23:59:59');
-------------------------------------------------------------
1
1

3 获取特定日期

3.1 获取当前日期

获取当前日期可以使用 CURDATE()
、CURRENT_DATE()
、SYSDATE()
等函数。

同样的，获取当前时间可以使用 CURTIME()
、CURRENT_TIME()
、NOW()
、CURRENT_TIMESTAMP()
、SYSDATE()、LOCALTIME()
、LOCALTIMESTAMP()
。

3.2 获取年月日时分秒

SET @now:='2020-01-28 17:28:02';
SELECT YEAR(@now);
SELECT MONTH(@now);
SELECT DAY(@now);
SELECT HOUR(@now);
SELECT MINUTE(@now);
SELECT SECOND(@now);
----------------------------------
2020
1
28
17
28
2

3.3 其它

# 获取本月第一天
SELECT DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY);
# 获取本月最后一天
SELECT LAST_DAY(CURDATE());
# 获取今年第一天
SELECT DATE_SUB(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY);
SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-01-01'),'%Y-%m-%d');
# 获取今年最后一天
SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY),INTERVAL 1 YEAR);
SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-12-31'),'%Y-%m-%d');