mysql日期函_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)

持续补充中~