mysql日期函数

日期操作是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)

持续补充中~