# DBeaver

如果使用DBeaver软件学习,需要在编辑连接设置正确时区。

image-20190713202236634

# 数据类型

日期时间类型 占用空间 日期格式 最小值 最大值 零值表示
DATETIME 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 08:00:01 2038-01-19 03:14:07 00000000000000
DATE 4 bytes YYYY-MM-DD 1000-01-01 9999-12-31 0000-00-00
TIME 3 bytes HH:MM:SS -838:59:59 838:59:59 00:00:00
YEAR 1 bytes YYYY 1901 2155 0000
  • Mysql保存日期格式使用 YYYY-MM-DD HH:MM:SS的ISO 8601标准
  • 向数据表储存日期与时间必须使用ISO格式

# 创建字段

ALTER TABLE stu ADD birthday datetime default null;

# 格式化

# 参数介绍

格式 描述
%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;

# 时间戳

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

设置TIMESTAMP字段

alter table stu add updated_at 
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

insert into stu set sname = '后盾人',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 = 'ih' where id= 7;
  • 执行更新或添加都会改变timestamp字段

# 常用函数

下面是获取当前日期、时间的示例

select CURRENT_DATE,CURRENT_TIME,NOW();

获取时间部分值

select YEAR(updated_at),MONTH(updated_at),DAY(updated_at) from stu;

其他可以使用的函数如下

函数 说明
HOUR 小时
MINUTE
SECOND
YEAR
MONTH
DAY
TIME 获取时间
WEEK
QUARTER
CURRENT_DATE(CURDATE) 当前日期
CURRENT_TIME 当前时间
NOW 当前时间
DAYOFYEAR 一年中的日数
DAYOFMONTH 月份中日数
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;

# 基本查询

查找在 1990~1999年出生的同学

SELECT * FROM stu WHERE birthday BETWEEN '1990-01-01' AND '1999-12-31';

获取年龄最大的同学

SELECT * FROM stu ORDER BY birthday ASC LIMIT 1;
# 更准确的查询
SELECT * FROM stu 
WHERE birthday =(SELECT birthday FROM stu ORDER BY birthday ASC LIMIT 1);

查询在1班或2班1990年出生的同学

SELECT * FROM stu WHERE class_id IN(1,2) AND year(birthday)=1999;

90后最喜欢上的班级

SELECT count(id) as total,class_id from stu 
WHERE LEFT(birthday,4) >= 1990 AND YEAR(birthday)<=2000 
GROUP BY class_id
ORDER BY total DESC
LIMIT 1; 

大于20岁的女生最多的班级

SELECT count(id),class_id FROM stu 
WHERE TIMESTAMPDIFF(YEAR,birthday,now())>20 AND sex=2
GROUP BY class_id
ORDER BY count(id) DESC
LIMIT 1;

# 时间计算

函数 说明
ADDTIME 添加时间(负数为减少),只对时间有效
TIMESTAMP 添加时间(负数为减少),只对时间有效
DATE_ADD 根据单位添加时间,支持单位有YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/HOUR_MINUTE(负数时等于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())-1 DAY) 
AND created_at <=LAST_DAY(now());

因为使用大量函数会造成性能下降,所以推荐在PHP中算出时间后比对

获取三个月内发表的文章

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

# 如果从前上个月的一号开始获取
SELECT * from article 
WHERE publish_time >= DATE_FORMAT(DATE_SUB(now(),INTERVAL 3 MONTH),'%Y-%m-01');

获取上个月的最后一天

SELECT LAST_DAY(DATE_SUB(NOW(),INTERVAL 1 MONTH));
# 或直接通过明确日期
SELECT * FROM article WHERE publish_time>='2019-01-01' AND publish_time<CURDATE();

下个月的第一天

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

查看出生超过20年有同学

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

本周二的日期

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

查看三周前的周二

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

上周一的日期

SET @week = DATE_SUB(NOW(),interval 1 week);
SELECT DATE_ADD(@week,INTERVAL 0-WEEKDAY(@week) 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));