【MySQL】时间函数

获取当前日期时间函数

now()	获得当前日期+时间（date + time）

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-06-20 11:26:20 |
+---------------------+
1 row in set

sysdate()	获得当前日期+时间（date + time）

与now()的不同之处在于：它获得的时间是执行sysdate()函数的时间，now()获得的时间是执行SQL的时间。示例：

mysql> select now(), sleep(3), sysdate(), now();
+---------------------+----------+---------------------+---------------------+
| now()               | sleep(3) | sysdate()           | now()               |
+---------------------+----------+---------------------+---------------------+
| 2018-06-20 11:32:15 |        0 | 2018-06-20 11:32:18 | 2018-06-20 11:32:15 |
+---------------------+----------+---------------------+---------------------+
1 row in set

current_timestamp、current_timestamp()	获得当前时间戳。与now()获取的时间一致。

mysql> select current_timestamp,current_timestamp(), sleep(3),now(),current_timestamp;

+---------------------+---------------------+----------+---------------------+---------------------+
| current_timestamp   | current_timestamp() | sleep(3) | now()               | current_timestamp   |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2018-06-20 11:39:40 | 2018-06-20 11:39:40 |        0 | 2018-06-20 11:39:40 | 2018-06-20 11:39:40 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set

日期转换函数、时间转换函数

DATE_FORMAT(date,format)	date根据format字符串格式化值。

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

有关format的更多格式，参考下表：

STR_TO_DATE(str,format)	与DATE_FORMAT(date,format)功能相反

如果格式字符串包含日期和时间部分，则STR_TO_DATE()返回DATETIME类型值；如果字符串仅包含日期或时间部分，则返回DATE值或TIME值；如果从中提取的日期，时间或日期时间值是非法的，则返回NULL。

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
        -> '2004-04-31'

mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
        -> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
        -> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
        -> '09:30:17'

未指定的日期或时间部分的值默认为0：

mysql> SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'

返回NULL的情况：

mysql> select str_to_date('01.5,2013','%d,%m,%Y');
+-------------------------------------+
| str_to_date('01.5,2013','%d,%m,%Y') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set

to_days(date)，from_days(days)	日期转换为天数与天数转换为日期

select to_days('0000-00-00'); -- 0
select to_days('2008-08-08'); -- 733627

time_to_sec(time)，sec_to_time(seconds)	时间转换为秒与秒转换为时间

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

makedate(year,dayofyear)，maketime(hour,minute,second)	拼凑日期、时间

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'

日期时间计算函数

date_add()	为日期增加一个时间间隔

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day

adddate()，addtime()函数可以用date_add()来替代。下面是date_add()实现addtime()功能示例：

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

date_sub()	为日期减去一个时间间隔

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+----------------------------------------------------------------+

datediff(date1,date2)，timediff(time1,time2)	日期、时间相减

datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。

select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

timediff(time1,time2)：两个时间相减 time1 - time2，返回 time 差值。

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

注意：timediff(time1,time2) 函数的两个参数类型必须相同。

timestamp()、timestampadd()、timestampdiff	时间戳转换、加、减

timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --

示例部分：

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

--MySQL timestampadd() 函数类似于 date_add()。
select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

timestampdiff()函数比datediff()功能多，datediff()只能计算两个日期（date）之间的天数。

获取日期时间的指定值

• DAYOFWEEK(date)，获取星期。

SELECT DAYOFWEEK(‘2018-6-20’) 
-> 4    (表示，记住：星期天=1，星期一=2， ... 星期六=7)

• WEEKDAY(date)，返回日期的星期索引(0=周一，1=周二，6=星期日)。

SELECT WEEKDAY(‘2018-6-20’) 
-> 2

• DAYOFMONTH(date)，返回值是当月的第几天。

SELECT DAYOFMONTH(‘2018-6-20’) 
-> 20

• DAYOFYEAR(date)，返回值是当年的第几天。

SELECT DAYOFYEAR(‘2018-6-20’) 
-> 171

• MONTH(date)，返回日期的月份。

SELECT MONTH(‘2018-6-20’) 
-> 6

类似的函数有：YEAR(date)、HOUR(time)、MINUTE(time)、SECOND(time)

• DAYNAME(date)，返回日期中星期的英文。

SELECT DAYNAME(‘2018-6-20’) 
-> Wednesday

• MONTHNAME(date)，返回日期中月份的英文。

SELECT MONTHNAME(‘2018-6-20’) 
-> June   

• QUARTER(date)，返回date代表的季度（1，2，3，4）。

SELECT QUARTER(‘2018-6-20’) 
-> 2

• WEEK(date)，此函数返回日期的周数。（传送门）

SELECT WEEK(‘2018-6-20’) 
-> 24

参考资料：https://www.cnblogs.com/ggjucheng/p/3352280.html
参考资料：https://blog.csdn.net/zhongqi2513/article/details/50528347
参考资料：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format