MySQL 日期函数

一、日期和时间类型：

DATE：YYYY-MM-DD 范围1000-01-01/9999-12-31

TIME: HH:MM:SS 范围'-838:59:59'/'838:59:59'

YEAR：YYYY 范围

DATETIME： YYYY-MM-DD hh:mm:ss 范围'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'

TIMESTAMP：YYYY-MM-DD hh:mm:ss 范围'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC

结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

datetime与timestamp的区别

1、两者的存储方式不一样:

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

2、存储字节大小不同

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。

当然，对于跨时区的业务，TIMESTAMP更为合适

二、日期函数

MySQL -uroot -p

创建表

CREATE TABLE attendance(id INT(8) PRIMARY KEY,stu_id INT(8),at_time DATETIME);

输入数据

INSERT INTO attendance1 VALUES(1,2,'2022-09-15 08:22:00'),(2,2,'2022-09-15 08:22:00'),(3,2,'2022-09-15 08:22:00'),(4,2,'2022-09-15 08:22:00'),(5,2,'2022-09-15 09:22:00'),(6,3,'2022-09-15 07:22:00'),(7,4,'2022-09-15 10:55:00'),(8,5,'2022-09-16 07:22:00'),(9,2,'2022-09-15 11:22:00'),(10,1,'2022-09-16 08:22:00'),(11,2,'2022-09-25 08:15:00'),(12,4,'2022-09-09 08:22:00');

更改表数据：

UPDATE attendance SET at_time="2022-09-24 08:05:00" WHERE id =2;

查询近30天内迟到的明细：

SELECT * FROM attendance WHERE TIME(at_time)>"08:30:00" AND DATE(at_time)>=DATE_SUB(CURRENT_DATE(),INTERVAL 30 DAY);

添加列：

ALTER TABLE attendance ADD hiredate DATE;

添加列值

UPDATE attendance SET hiredate="2001-01-03";或者UPDATE attendance SET hiredate="2001-02-03" where id=2；

1. ADDDATE(d,n)计算起始日期 d 加上 n 天的日期：

SELECT *,ADDDATE(DATE(hiredate),INTERVAL 10 DAY) AS date2 FROM attendance;

2.ADDTIME(t,n) n 是一个时间表达式，时间 t 加上时间表达式 n 单位为秒（0s-60s之间）：

SELECT *,ADDTIME(DATE_FORMAT(at_time,"%Y-%m-%d %r"),55) AS time2 FROM attendance;

SELECT *,ADDTIME(DATE_FORMAT(at_time,"%Y-%m-%d %r"),60) AS time2 FROM attendance;

但是可：SELECT *,ADDTIME(DATE_FORMAT(at_time,"%Y-%m-%d %r"),"02:10:10") AS time2 FROM attendance;

SELECT *,ADDTIME(DATE_FORMAT(at_time,"%Y-%m-%d %r"),"00:00:20") AS time2 FROM attendance;

3.DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数:

SELECT *,DATEDIFF(DATE(at_time),DATE(hiredate)) AS days,ROUND(DATEDIFF(DATE(at_time),DATE(hiredate))/365,1) AS years FROM attendance;

4.DATE_ADD(d，INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期，type 值可以是：

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK 等等

加减天--SELECT *,DATE_ADD(DATE(at_time),INTERVAL 10 DAY) AS addays FROM attendance;

加减分钟--SELECT *,DATE_ADD(TIMESTAMP(at_time),INTERVAL 10 MINUTE) AS addminutert FROM attendance;

加减小时--SELECT *,DATE_ADD(TIMESTAMP(at_time),INTERVAL -2 HOUR) AS subhour FROM attendance;

加减月--SELECT * ,DATE_ADD(TIMESTAMP(at_time),INTERVAL 4 MONTH) AS addmonth FROM attendance;

5.DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d：

SELECT *,DATE_FORMAT(at_time,"%Y-%m-%d") AS date2 FROM attendance;

SELECT *,DATE_FORMAT(hiredate,"%Y-%m-%d %r") AS date2 FROM attendance;

SELECT *,DATE_FORMAT(at_time,"%m") AS month2 FROM attendance;

6.DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。

SELECT *,DATE_SUB(at_time,INTERVAL 10 DAY) AS subdays FROM attendance;

SELECT *,DATE_SUB(DATE_FORMAT(at_time,"%Y-%m-%d"),INTERVAL 10 DAY) AS subdays FROM attendance;

7.DAY(d)

：

SELECT *,DAY(at_time) AS days FROM attendance;

8.DAYNAME(d)

9.DAYOFMONTH(d) 计算日期 d 是本月的第几天；DAYOFWEEK(d) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推；DAYOFYEAR(d) 计算日期 d 是本年的第几天：

SELECT *,DAYOFMONTH(at_time) AS days,DAYOFWEEK(at_time) AS weeknumber,DAYOFYEAR(at_time) AS sday FROM attendance;

10.LAST_DAY(d) 返回给给定日期的那一月份的最后一天：

SELECT *,LAST_DAY(at_time) AS last_days FROM attendance;

11、》》》》

SELECT NOW() 返回当前日期和时间
-> 2018-09-19 20:57:43

SELECT PERIOD_ADD(201703, 5); 为 年-月 组合日期添加一个时段
-> 201708

SELECT QUARTER('2011-11-11 11:11:11') 返回日期d是第几季节，返回 1 到 4
-> 4

SELECT SUBDATE('2011-11-11 11:11:11', 1) 日期 d 减去 n 天后的日期

->2011-11-10 11:11:11

SELECT YEARWEEK("2017-06-15");返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推
-> 201724

SELECT WEEKOFYEAR('2011-11-11 11:11:11')计算日期 d 是本年的第几个星期，范围是 0 到 53
-> 45