日期操作是mysql中的常用操作，掌握常用的日期函数、并熟练组合运用能够帮助我们解决查询中的许多难题。
本次笔记主要介绍常用的日期函数，在之后的笔记中可能会介绍几个稍微复杂的日期函数组合使用场景。
笔记大纲：
~~·获取当前日期与时间
~~·获取日期或时间中的某一部分
~~·日期运算
1、获取当前日期与时间
now()、current_timestamp() —— 获取系统执行该语句的日期与时间
sysdate() —— 获取执行到该函数的日期与时间
current_date() —— 获取系统当前日期
current_time() —— 获取系统当前时间
unix_timestamp(now()) —— 获取当前时间戳
mysql> select now(),current_timestamp(),sysdate();
+---------------------+---------------------+---------------------+
| now() | current_timestamp() | sysdate() |
+---------------------+---------------------+---------------------+
| 2018-10-09 18:07:12 | 2018-10-09 18:07:12 | 2018-10-09 18:07:12 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2018-10-09 |
+----------------+
1 row in set (0.00 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 18:00:30 |
+----------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1539079679 |
+-----------------------+
1 row in set (0.00 sec)
2、获取日期或时间中的某一部分
年 year()、月 month()、日 day()
时 hour()、分 minute()、秒 second()
mysql> select year(now()),month(now()),day(now());
+-------------+--------------+------------+
| year(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
| 2018 | 10 | 9 |
+-------------+--------------+------------+
1 row in set (0.00 sec)
mysql> select hour(now()),minute(now()),second(now());
+-------------+---------------+---------------+
| hour(now()) | minute(now()) | second(now()) |
+-------------+---------------+---------------+
| 18 | 13 | 57 |
+-------------+---------------+---------------+
1 row in set (0.00 sec)
获取日期/时间中月名、周名等
monthname(date)
dayname(date)
mysql> select monthname('1990-05-07 13:00:00') as month_name,
-> dayname('1990-05-07 13:00:00') as day_name;
+------------+----------+
| month_name | day_name |
+------------+----------+
| May | Monday |
+------------+----------+
1 row in set (0.00 sec)
除了以上函数，还可用date_format获取日期/时间中的某部分，该函数也可输出指定格式的日期/时间。
mysql> select date_format('1990-05-07 23:24:15', '%Y') as '年',
-> date_format('1990-05-07 23:24:15', '%m') as '月',
-> date_format('1990-05-07 23:24:15', '%d') as '日',
-> date_format('1990-05-07 23:24:15', '%H') as '时',
-> date_format('1990-05-07 23:24:15', '%m') as '分',
-> date_format('1990-05-07 23:24:15', '%s') as '秒';
+------+------+------+------+------+------+
| 年 | 月 | 日 | 时 | 分 | 秒 |
+------+------+------+------+------+------+
| 1990 | 05 | 07 | 23 | 05 | 15 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)
mysql> select date_format('1990-05-07 23:24:15','%Y%m%d') as date;
+----------+
| date |
+----------+
| 19900507 |
+----------+
1 row in set (0.00 sec)
3、日期运算
datediff(date1,date2) —— 计算日期差(date1-date2)
timediff(time1,time2) —— 计算时间差(time1-time2)
mysql> select datediff('1990-05-07 13:00:00','1990-05-08 13:00:00');
+-------------------------------------------------------+
| datediff('1990-05-07 13:00:00','1990-05-08 13:00:00') |
+-------------------------------------------------------+
| -1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select timediff('1990-05-07 13:00:00','1990-05-08 13:00:00');
+-------------------------------------------------------+
| timediff('1990-05-07 13:00:00','1990-05-08 13:00:00') |
+-------------------------------------------------------+
| -24:00:00 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
指定值的日期运算
date_add(date, interval (-)n year/month/day等)
mysql> select date_add('1990-05-07 13:00:00', interval 5 year);
+--------------------------------------------------+
| date_add('1990-05-07 13:00:00', interval 5 year) |
+--------------------------------------------------+
| 1995-05-07 13:00:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1990-05-07 13:00:00', interval -5 day);
+--------------------------------------------------+
| date_add('1990-05-07 13:00:00', interval -5 day) |
+--------------------------------------------------+
| 1990-05-02 13:00:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1990-05-07 13:00:00', interval 10 minute);
+-----------------------------------------------------+
| date_add('1990-05-07 13:00:00', interval 10 minute) |
+-----------------------------------------------------+
| 1990-05-07 13:10:00 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
获取该月的最后一天
last_day(date)
mysql> select last_day('1990-05-07 13:00:00');
+---------------------------------+
| last_day('1990-05-07 13:00:00') |
+---------------------------------+
| 1990-05-31 |
+---------------------------------+
1 row in set (0.00 sec)
dayofyear(date) —— date位于全年中的第几天
dayofmonth(date) —— date位于该月中的第几天
dayofweek(date) —— date位于该周中的第几天（1表示星期天）
mysql> select dayofyear('1990-05-07 13:00:00') as year_num,
-> dayofmonth('1990-05-07 13:00:00') as month_num,
-> dayofweek('1990-05-07 13:00:00') as week_num;
+----------+-----------+----------+
| year_num | month_num | week_num |
+----------+-----------+----------+
| 127 | 7 | 2 |
+----------+-----------+----------+
1 row in set (0.00 sec)
持续补充中~
