mysql定义日期（MySQL日期函数）
mysql定义日期（MySQL日期函数）datetime与timestamp的区别结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:071901/2155DATETIME： 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
一、日期和时间类型：
DATE：YYYY-MM-DD 范围1000-01-01/9999-12-31
TIME: HH:MM:SS 范围'-838:59:59'/'838:59:59'
YEAR：YYYY 范围
|
1901/2155 |
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、存储字节大小不同
|
数据类型 |
MySQL 5.6.4之前需要存储 |
MySQL 5.6.4之后需要存储 |
|
DATETIME |
8 bytes |
5 bytes 小数秒存储 |
|
TIMESTAMP |
4 bytes |
4 bytes 小数秒存储 |
总结：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；
- 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)
|
返回日期值 d 的日期部分 |
：
SELECT * DAY(at_time) AS days FROM attendance;
8.DAYNAME(d)
|
返回日期 d 是星期几，如 Monday Tuesday |
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
