mysql-日期类型

学习网站

https://houdunren.gitee.io/note/mysql/5%20%E6%97%A5%E6%9C%9F%E6%97%B6%E9%97%B4.html#数据类型

设置时区

https://blog.csdn.net/aa15237104245/article/details/83345232

设置为东八区，也就是北京时间。

set global time_zone = '+8:00';

数据类型

Mysql保存日期格式使用 YYYY-MM-DD HH:MM:SS的ISO 8601标准
向数据表储存日期与时间必须使用ISO格式

格式化方式

%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位 

使用DATE_FORMAT格式化日期与时间显示。

select sname,DATE_FORMAT(birthday,'%Y年%m月%d %H时%i分%s秒') as birthday from stu;

使用TIME_FORMAT格式化输出时间，（只能处理时分秒）。

select sname,TIME_FORMAT(birthday,'%r') as birthday from stu;

time_format和Date_format的区别。
https://blog.csdn.net/weixin_43354181/article/details/103759207

TIMESTAMP时间戳

以时间戳格式来记录日期与时间。

设置TIMESTAMP字段

ALTER TABLE stu ADD updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
INSERT INTO stu SET sname = '123',updated_at = '2020-2-12 10:33:12';

添加数据时自动更新时间

ALTER TABLE stu ADD updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
insert into stu set sname = '小张';
update stu set sname = '小王' where id= 7;

当执行添加与更新时字段将自动为当前时间，执行更新或添加都会改变timestamp字段

函数

函数	说明
HOUR	时（范围从0到23）
MINUTE	分（范围从0到59）
SECOND	秒（范围从0到59）
YEAR	年（范围从1000 到 9999）
MONTH	月（范围从1到12）
DAY	日（范围从1开始）
TIME	获取时间
WEEK	一年中的第几周，从1开始计数
QUARTER	一年中的季度，从1开始计数
CURRENT_DATE	当前日期
CURRENT_TIME	当前时间
NOW	当前时间
DAYOFYEAR	一年中的第几天（从1开始）
DAYOFMONTH	月份中天数（从1开始）
DAYOFWEEK	星期日（1）到星期六（7）
WEEKDAY	星期一（0）到星期天（6）
TO_DAYS	从元年到现在的天数（忽略时间部分）
FROM_DAYS	根据天数得到日期（忽略时间部分）
TIME_TO_SEC	时间转为秒数（忽略日期部分）
SEC_TO_TIME	根据秒数转为时间（忽略日期部分）
UNIX_TIMESTAMP	根据日期返回秒数（包括日期与时间）
FROM_UNIXTIME	根据秒数返回日期与时间（包括日期与时间）
DATEDIFF	两个日期相差的天数（忽略时间部分，前面日期减后面日期）
TIMEDIFF	计算两个时间的间隔（忽略日期部分）
TIMESTAMPDIFF	根据指定单位计算两个日期时间的间隔（包括日期与时间）
LAST_DAY	该月的最后一天

拆分日期时间

select sname,YEAR(birthday),MONTH(birthday),DAY(birthday),HOUR(birthday),
MINUTE(birthday),SECOND(birthday) from stu;

当前日期时间

SELECT now(),CURDATE(),CURRENT_DATE(),CURRENT_TIME(),NOW();

时间计算

SELECT DAYOFYEAR(now()),DAYOFMONTH(now()),DAYOFWEEK(now()),WEEKDAY(now());

秒转换，不包含日期的秒转换

SET @time = time(now());
SELECT now(),TIME_TO_SEC(@time),SEC_TO_TIME(TIME_TO_SEC(@time));

日期时间与秒转换

SELECT now(),UNIX_TIMESTAMP(birthday),FROM_UNIXTIME(UNIX_TIMESTAMP(birthday)) FROM stu;

天转换

SELECT now(),TO_DAYS(birthday),FROM_DAYS(TO_DAYS(birthday)) FROM stu;

差值计算
计算天数差值，忽略时间部分

SELECT now(),DATEDIFF(now(),birthday) from stu;

计算时间差值，忽略天数

SELECT now(),TIMEDIFF(time(birthday),time(now())) from stu;

指定单位差值
支持的单位有 YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND等。下面是获取学生来到人生经历的天数。

select sname,TIMESTAMPDIFF(day,birthday,NOW()) from stu;

时间计算

ADDTIME	添加时间（负数为减少），只对时间有效 
DATE_ADD	根据单位添加时间，支持单位有YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/HOUR_MINUTE/DAY_HOUR/DAY_MINUTE/DAY_SECOND/HOUR_MINUTE/HOUR_SECOND
DATE_SUB  根据单位减少时间，支持单位同上。
DATE_SUB	DATE_ADD的反函数
LAST_DAY	指定月最后一天日期

七小时前的时间

select ADDTIME(now(),'-7:00:00')

七天后的日期

SELECT DATE_ADD(now(),INTERVAL 7 DAY);

20小时10分钟后的日期

SELECT DATE_ADD(NOW(),INTERVAL '20:10' HOUR_MINUTE);

2天8小时后的日期

SELECT DATE_ADD(NOW(),INTERVAL '2 8' DAY_HOUR);

获取本月最后一天日期

SELECT LAST_DAY(now());

获取本月的第一天日期

SELECT DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY);

获取本月发表的文章

SELECT * FROM article 
WHERE created_at >=DATE_SUB(now(),INTERVAL DAYOFMONTH(now()) DAY) 
AND created_at <=LAST_DAY(now());

因为使用大量函数会造成性能下降，所以推荐在后台程序中算出时间后再进行比对

获取三个月内发表的文章

SELECT * FROM article 
WHERE publish_time >=DATE_SUB(now(),INTERVAL -3 MONTH) ;   

获取上个月的最后一天

SELECT LAST_DAY(DATE_SUB(NOW(),INTERVAL 1 MONTH));

下个月的第一天

SELECT DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 DAY);

查看出生超过20年的同学

SELECT * FROM stu WHERE birthday < DATE_SUB(NOW(),INTERVAL 20 YEAR);

本周二的日期，使用DAYOFWEEK时周二为3

SELECT now(),DATE_ADD(NOW(),INTERVAL 3-DAYOFWEEK(NOW()) DAY);

本周星期日

SELECT date_add(now(),INTERVAL 6-WEEKDAY(now()) DAY)  

上周的星期日

SELECT date_add(now(),INTERVAL 1-DAYOFWEEK(now()) day) 

查看三周前的周二

SELECT now(),
DATE_SUB(DATE_ADD(NOW(),INTERVAL 3-DAYOFWEEK(NOW()) DAY),INTERVAL 21 DAY);

# 或
SELECT date_add(now(),INTERVAL 3-DAYOFWEEK(now())-21 DAY )

上周一的日期

SET @week = DATE_SUB(NOW(),interval 1 week);
SELECT DATE_ADD(@week,INTERVAL 0-WEEKDAY(@week) day);

#或
SELECT date_add(now(),INTERVAL 2-DAYOFWEEK(now())-7 DAY )

获取本月迟到的同学

select * from attendance 
WHERE created_at >= date_sub(NOW(),INTERVAL DAYOFMONTH(now())-1 DAY)
AND time(created_at)>'08:30:00';
 

本月迟到超过2次的同学

select stu_id from attendance 
WHERE created_at >= date_sub(NOW(),INTERVAL DAYOFMONTH(now())-1 DAY)
AND time(created_at)>'08:30:00'
GROUP BY stu_id
HAVING count(id)>=2;

本周周一的日期

SELECT DATE_ADD(now(),INTERVAL 0-WEEKDAY(now()) day);

获取本周迟到的学生编号

set @begin =DATE_FORMAT(date_add(now(),INTERVAL 0-WEEKDAY(now()) day),'%Y-%m-%d');
select stu_id from attendance 
WHERE created_at >= @begin
AND time(created_at)>'08:00:00'
GROUP BY stu_id;

获取上周打卡记录

set @week = DATE_SUB(now(),INTERVAL 1 WEEK);
SELECT stu_id FROM attendance
WHERE created_at>=DATE_ADD(@week,interval 0-WEEKDAY(@week) day)
AND created_at<=DATE_ADD(@week,interval 4-WEEKDAY(@week) day);

获取本周发表的文章

SELECT * FROM article WHERE created_at >=DATE_ADD(now(),INTERVAL 0-WEEKDAY(now()) day);

周日来校学习的同学

SELECT * from attendance
WHERE date(created_at) = date(date_add(now(),INTERVAL 6 - WEEKDAY(now()) day));