MySQL入门命令之函数-单行函数-日期函数

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)

更多MySQL命令