日期函数
获取当前日期,当前时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-06-09 23:21:10 |
+---------------------+
1 row in set (0.00 sec)
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-06-09 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 23:21:23 |
+-----------+
1 row in set (0.00 sec)
截取时间
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2020 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 6 |
+--------------+
1 row in set (0.00 sec)
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)
mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 23 |
+-------------+
1 row in set (0.00 sec)
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 24 |
+---------------+
1 row in set (0.00 sec)
mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 32 |
+---------------+
1 row in set (0.00 sec)
案例:
mysql> select (year(now())-year(‘1998-01-01 00:01:01‘));
+-------------------------------------------+
| (year(now())-year(‘1998-01-01 00:01:01‘)) |
+-------------------------------------------+
| 22 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select (year(‘1998-01-01 00:01:01‘));
+-------------------------------+
| (year(‘1998-01-01 00:01:01‘)) |
+-------------------------------+
| 1998 |
+-------------------------------+
1 row in set (0.00 sec)
将字符串转换成日期
mysql> select str_to_date();
mysql> select str_to_date(‘1-1 2020‘,‘%m-%d %y‘);
+------------------------------------+
| str_to_date(‘1-1 2020‘,‘%m-%d %y‘) |
+------------------------------------+
| 2020-01-01 |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)
日期格式符
格式符 | 作用 |
%Y | 4位年份,例如:1998 |
%y | 2位年份,例如:98 |
%m | 月份,例如: 01,02,...,12 |
%c | 月份,例如: 1,2,...,12 |
%d | 日期,例如01,02,..,31 |
%H | 24小时制 |
%h | 12小时制 |
%i | 分钟,例如:00-59 |
%s | 秒,例如:00-59 |
以指定的字符串格式显示日期
mysql> select date_format(now(),‘%Y-%m-%d‘);
+-------------------------------+
| date_format(now(),‘%Y-%m-%d‘) |
+-------------------------------+
| 2020-06-09 |
+-------------------------------+
1 row in set (0.00 sec)