MySQL基础教程12——函数——日期函数
curdate(当前日期)
select curdate();
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2022-03-15 |
+------------+
1 row in set
curtime(当前时间)
select curtime();
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:27:39 |
+-----------+
1 row in set
now(当前日期及时间)
select now();
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-03-15 14:28:20 |
+---------------------+
1 row in set
year(获取指定年份)
select year(date);
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2022 |
+-------------+
1 row in set
month(获取指定月份)
select month(date);
mysql> select month
(now());
+--------------+
| month(now()) |
+--------------+
| 3 |
+--------------+
1 row in set
day(获取指定日期)
select day(date);
mysql> select day
(now());
+------------+
| day(now()) |
+------------+
| 15 |
+------------+
1 row in set
date_add(返回一个日期/时间加上一个时间间隔expr后的时间值)
select date_add(date,interval expr type);
mysql> select date_add(now(),interval 80 day);
+---------------------------------+
| date_add(now(),interval 80 day) |
+---------------------------------+
| 2022-06-03 14:37:53 |
+---------------------------------+
1 row in set
mysql> select date_add(now(),interval 80 month);
+-----------------------------------+
| date_add(now(),interval 80 month) |
+-----------------------------------+
| 2028-11-15 14:38:07 |
+-----------------------------------+
1 row in set
mysql> select date_add(now(),interval 80 year);
+----------------------------------+
| date_add(now(),interval 80 year) |
+----------------------------------+
| 2102-03-15 14:38:17 |
+----------------------------------+
1 row in set
datediff(返回其实日期date1和结束日期date2之间的天数)
select datediff(date1,date2);
mysql> select datediff('2021-3-15',now());
+-----------------------------+
| datediff('2021-3-15',now()) |
+-----------------------------+
| -365 |
+-----------------------------+
1 row in set
mysql> select datediff('2022-3-15','2022-5-30
');
+-----------------------------------+
| datediff('2022-3-15','2022-5-30') |
+-----------------------------------+
| -76 |
+-----------------------------------+
1 row in set
注意： 以上内容如果类型或者值填写错误不会报错但会显示NULL！
案例
查询员工入职天数并且倒叙排序。
使用datediff查询入职时间与现在的差距天数
mysql> select * from user;
+------------+------+
| entry | name |
+------------+------+
| 2021-05-15 | 张三 |
| 2021-03-15 | 李四 |
| 2021-09-07 | 王五 |
+------------+------+
3 rows in set
mysql> select name,datediff(curdate(),entry) as entry from user order by entry desc;
+------+-------+
| name | entry |
+------+-------+
| 李四 | 365 |
| 张三 | 304 |
| 王五 | 189 |
+------+-------+
3 rows in set
