sql 时间函数

1，前提

今天看同事写的sql里面出现了时间类的函数，平时自己也经常用到，每次都要百度，还不如自己整理记录在一起，方便后续使用。

2，sql时间函数

2.1 获取当前时间：

select
	NOW() as '当前日期+时间',
	CURDATE() as '当前日期',
	CURTIME() as '当前时间';
1
2
3
4

2.2 获取单个年月日时分秒

SELECT  
  YEAR(now() ) AS '年',
  MONTH(now()) AS '月',
  DAY(now()) AS '日',
  HOUR(now()) AS '时',
  MINUTE(now()) AS '分',
  SECOND(now()) AS '秒',
  DAYNAME(now()) AS '星期';
1
2
3
4
5
6
7
8

2.3 格式化日期

SELECT
  NOW() AS "当前时间",
  DATE_FORMAT(NOW(), '%Y.%m.%d') AS '格式化日期',
  DATE_FORMAT(NOW(), '%Y/%m/%d') AS '格式化日期',
  DATE_FORMAT(NOW(), '%Y-%m-%d') AS '格式化日期',
  TIME_FORMAT(NOW(), '%H:%i:%s') AS '格式化时间';
1
2
3
4
5
6

2.4 计算日期

select
	NOW() as "现在时间",
	DATE_ADD(NOW(), interval 1 year) as "增加1年",
	DATE_ADD(NOW(), interval 1 month) as "增加1个月",
	DATE_ADD(NOW(), interval 1 day) as "增加1天",
	DATE_ADD(NOW(), interval 1 hour) as "增加1小时",
	DATE_SUB(NOW(), interval 1 year) as "减少1年",
	DATE_SUB(NOW(), interval 1 month) as "减少1个月",
	DATE_SUB(NOW(), interval 1 day) as "减少1天",
	DATE_SUB(NOW(), interval 1 hour) as "减少1小时";
1
2
3
4
5
6
7
8
9
10

select
	NOW() as "现在时间",
	DATE_ADD(NOW(), interval 1 year) as "增加1年",
	DATEDIFF(NOW(), DATE_ADD(NOW(), interval 1 year)) as "间隔天数";
1
2
3
4

好文推荐：

https://zhuanlan.zhihu.com/p/533691995