1、now 返回当前系统日期+时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-04-04 15:44:39 |
+---------------------+
1 row in set (0.00 sec)
2、curdate 返回系统当前日期,不包含时间
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-04-04 |
+------------+
1 row in set (0.00 sec)
3、cuetime 返回系统当前时间,不包含日期
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:45:45 |
+-----------+
1 row in set (0.00 sec)
4、year–单独获取系统当前时间的年份
month–单独获取系统当前时间的月份
monthname–单独获取系统当前时间的月份的英文名称
day–单独获取系统当前时间的月份
hour–单独获取系统当前时间的月份
minute–单独获取系统当前时间的月份
second–单独获取系统当前时间的月份
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2021 |
+-------------+
1 row in set (0.00 sec)
mysql> select year('1998-1-1');
+------------------+
| year('1998-1-1') |
+------------------+
| 1998 |
+------------------+
1 row in set (0.00 sec)
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| April |
+------------------+
1 row in set (0.00 sec)
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 16 |
+-------------+
1 row in set (0.00 sec)
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 54 |
+---------------+
1 row in set (0.00 sec)
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
5、str_to_date 将字符串通过指定格式转换成日期
mysql> select str_to_date('1999-3-2','%Y-%c-%d') as date;
+------------+
| date |
+------------+
| 1999-03-02 |
+------------+
1 row in set (0.00 sec)
#实例
mysql> select last_name,hiredate from employees where hiredate=str_to_date('1992-4-3','%Y-%c-%d');
+-----------+---------------------+
| last_name | hiredate |
+-----------+---------------------+
| K_ing | 1992-04-03 00:00:00 |
| Kochhar | 1992-04-03 00:00:00 |
| De Haan | 1992-04-03 00:00:00 |
| Hunold | 1992-04-03 00:00:00 |
| Ernst | 1992-04-03 00:00:00 |
+-----------+---------------------+
5 rows in set (0.00 sec)
6、date_format 将字符转换为日期
mysql> select date_format(now(),'%y年%c月%d日');
+-----------------------------------+
| date_format(now(),'%y年%c月%d日') |
+-----------------------------------+
| 21年4月04日 |
+-----------------------------------+
1 row in set (0.00 sec)
#实例
mysql> select last_name,date_format(hiredate,'%m月%d日%y年') as入职时间
-> from employees
-> where hiredate=str_to_date('1992-4-3','%Y-%c-%d');
+-----------+--------------+
| last_name | as入职时间 |
+-----------+--------------+
| K_ing | 04月03日92年 |
| Kochhar | 04月03日92年 |
| De Haan | 04月03日92年 |
| Hunold | 04月03日92年 |
| Ernst | 04月03日92年 |
+-----------+--------------+
5 rows in set (0.00 sec)
7、datediff 查询两个日期之间的差值
mysql> select max(hiredate),min(hiredate),datediff(max(hiredate),min(hiredate)) as 差 from employees;
+---------------------+---------------------+------+
| max(hiredate) | min(hiredate) | 差 |
+---------------------+---------------------+------+
| 2016-03-03 00:00:00 | 1992-04-03 00:00:00 | 8735 |
+---------------------+---------------------+------+
1 row in set (0.00 sec)