时间函数

1/获取当前日期

mysql> select current_date(),curdate();
+----------------+------------+
| current_date() | curdate()  |
+----------------+------------+
| 2021-08-02     | 2021-08-02 |
+----------------+------------+
1 row in set (0.00 sec)

2/获取当前时间

mysql> select current_time,curtime();
+--------------+-----------+
| current_time | curtime() |
+--------------+-----------+
| 06:01:49     | 06:01:49  |
+--------------+-----------+
1 row in set (0.00 sec)

3/获取当前日期跟时间

mysql> select now(),sysdate();
+---------------------+---------------------+
| now()               | sysdate()           |
+---------------------+---------------------+
| 2021-08-02 06:02:36 | 2021-08-02 06:02:36 |
+---------------------+---------------------+
1 row in set (0.01 sec)

4/获取时间的月份

mysql> select month(current_date()),monthname(current_date());
+-----------------------+---------------------------+
| month(current_date()) | monthname(current_date()) |
+-----------------------+---------------------------+
|                     8 | August                    |
+-----------------------+---------------------------+
1 row in set (0.00 sec)

5/获取日期的英文名

mysql> select now(),dayname(now());
+---------------------+----------------+
| now()               | dayname(now()) |
+---------------------+----------------+
| 2021-08-02 06:05:43 | Monday         |
+---------------------+----------------+
1 row in set (0.00 sec)

6/获取给定日期是一周的第几天

mysql> select now(),dayofweek(now());
+---------------------+------------------+
| now()               | dayofweek(now()) |
+---------------------+------------------+
| 2021-08-02 21:02:04 |                2 |
+---------------------+------------------+
1 row in set (0.01 sec)

7/获取给定日期是一个月的第几天

mysql> select now(),dayofmonth(now());
+---------------------+-------------------+
| now()               | dayofmonth(now()) |
+---------------------+-------------------+
| 2021-08-03 04:43:57 |                 3 |
+---------------------+-------------------+
1 row in set (0.00 sec)

8/获取年份

mysql> select now(),year(now());
+---------------------+-------------+
| now()               | year(now()) |
+---------------------+-------------+
| 2021-08-03 04:44:29 |        2021 |
+---------------------+-------------+
1 row in set (0.00 sec)

9/获取两个日期之间的间隔，前面的减后面的

mysql> select datediff('2021-08-01','2021-09-01');
+-------------------------------------+
| datediff('2021-08-01','2021-09-01') |
+-------------------------------------+
|                                 -31 |
+-------------------------------------+
1 row in set (0.00 sec)

10/获取mysql的版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.30    |
+-----------+
1 row in set (0.00 sec)

11/获取当前数据库

mysql> select database();
+--------------------+
| database()         |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

12/获取当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

13/生成加密

mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)