MySQL内置函数-单行函数(日期函数)

日期函数

获取当前日期,当前时间

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 月份,例如: 0102,...,12
%c 月份,例如: 12,...,12
%d 日期,例如0102,..,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)

 

MySQL内置函数-单行函数(日期函数)

上一篇:专业技术test


下一篇:云原生下的指标与日志采集