Mysql 常用日期处理函数
1 建表语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`EMPNO` int(4) NOT NULL,
`ENAME` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`JOB` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`MGR` int(4) NULL DEFAULT NULL,
`HIREDATE` date NULL DEFAULT NULL,
`SAL` double(7, 2) NULL DEFAULT NULL,
`COMM` double(7, 2) NULL DEFAULT NULL,
`DEPTNO` int(2) NULL DEFAULT NULL,
PRIMARY KEY (`EMPNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
SET FOREIGN_KEY_CHECKS = 1;
2.计算两个日期之间的天数
2.1 语法
DATEDIFF(d1,d2)
2.2 示例sql语句
SELECT DATEDIFF('2022-02-02','2022-02-05')
FROM dual
2.3 运行截图
3 以不同的格式显示日期/时间数据
3.1 语法
DATE_FORMAT(d,f)
3.2 示例sql语句
SELECT ename,date_format(hiredate,'%Y-%m')
FROM emp
3.3 运行截图
4 从日期中变更指定的时间间隔
4.1 语法
DATE_SUB(date,INTERVAL expr type)
4.2 示例sql语句
4.2.1 当为expr为正数,返回过去日期
SELECT date_sub(hiredate,interval 2 day) day,hiredate
FROM emp
4.2.2 当为expr为负数,返回未来日期
SELECT date_sub(hiredate,interval -2 day) day,hiredate
FROM emp
4.3 运行截图
4.3.1 当为expr为正数
4.3.2 当为expr为负数时
5 返回当前日期和时间
5.1 语法
NOW()
5.2 示例sql语句
SELECT NOW()
FROM dual;
5.3 运行截图
6 计算起始日期 d 变更n天后的日期
6.1 语法
ADDDATE(d,n)
6.2 示例sql语句
SELECT hiredate,ADDDATE(hiredate,interval 2 day)
FROM emp
6.3 运行截图
7 返回指定日期那一月份的最后一天
7.1 语法
LAST_DAY(d)
7.2 示例sql语句
SELECT hiredate,LAST_DAY(hiredate) last
FROM emp;
7.3 运行截图
8 返回日期是第几季度
8.1 语法
QUARTER(d)
8.2 示例sql语句
SELECT hiredate,QUARTER(hiredate) '季度'
FROM emp;
8.3 运行截图