MySQL基础教程12——函数——日期函数
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
