8.时间日期

日期类型

• datetime 日期格式为ISO 8601标准 YYYY-MM-DD HH:MM:SS

-- 增加一个生产日期 create_date
ALTER TABLE goods add create_date Date
-- 增加一个更新日期
alter table goods add update_time datetime
update  goods  set  create_date = "2022-06-30" WHERE id = 5
update goods set update_time = "2022-06-30 14:28:30" WHERE id =5
-- 查询日期
SELECT shopName ,DATE_FORMAT(create_date,'%Y年=%m月=%d日') from goods g  
select shopName, TIME_FORMAT(update_time,'%H:%i:%s') from goods g  
-- 时间戳
alter table goods add update_at timestamp
update goods  set update_at = '2022-06-30 15:27:12' WHERE  id = 1
-- 添加数据时自动更新时间
alter table goods drop update_at
desc goods 
alter table goods  add update_at timestamp not null default current_timestamp on UPDATE current_timestamp
update goods set tags = 'Wi-Fi6' where id =6

时间函数

-- 一些时间函数
SELECT YEAR (create_date), MONTH (create_date),DAY (create_date) from goods g 

select HOUR (update_at),MINUTE (update_at),SECOND (update_at) from goods g 

select NOW(), CURRENT_DATE(),CURRENT_TIME()  

select DAYOFYEAR(NOW()) 
select DAYOFMONTH(NOW())
select DAYOFWEEK(NOW()) # 1-7
select WEEKDAY(NOW()) #星期一是0，星期日是6

create table article (id int auto_increment primary key , title varchar (100),publish_time datetime default null , status tinyint default 1)

insert into article (title , status) values ('123',1),('456',0)
insert into article (title, publish_time,status) values ('7889','2022-10-01',0)
insert into article (title, publish_time,status) values ('147','2022-3-01',0)


select *FROM article a WHERE status = 1

-- 查询哪些文章该发送了,publish_time 小于当前时间，并且status=0
SELECT  * from article a  WHERE publish_time < NOW() AND  status =0 
-- 发布文章
update article  set status =1 WHERE publish_time <NOW() AND  status = 0 

-- 设置一个变量
set @time = time(NOW())

SELECT @time

-- time转换成秒 ， 秒转换成time
select TIME_TO_SEC(@time) , SEC_TO_TIME(TIME_TO_SEC(@time)) 

-- 从元年到现在是多少天，从元年到现在的天数，转换成日期
select TO_DAYS(NOW()) ,FROM_DAYS(TO_DAYS(NOW())) 

-- 两个日期相差的时间 转成秒
select TIME_TO_SEC(TIMEDIFF(time(NOW()),time(update_at)))  from goods g
-- 两个时间戳相差的秒数
select timestampdiff(SECOND  , update_at,NOW()) from goods g

-- 查询两个日期中间的数据
select * FROM goods g WHERE update_at  BETWEEN '2010-01-01' AND '2020-01-01'

-- 时间排序
SELECT shopName ,create_date  from goods g order by create_date  DESC
-- 子查询
select * FROM goods g WHERE create_date = (SELECT create_date from goods g2 order by create_date  desc limit 1)

-- 查询02年创建的数据
select * FROM goods g  WHERE YEAR(create_date) = '2002'

select * FROM goods g  WHERE YEAR(create_date) = '2002' AND (id BETWEEN 1 and 5)  #IN (1,2,3,4,5,6,7)

SELECT  brandName  ,COUNT(id) as total  FROM  goods  WHERE YEAR (create_date) >= 2010 AND YEAR (create_date) <= 2030 
GROUP  by brandName 
ORDER by total DESC 
limit 1

-- 出厂时间在10年前大于10年的商品
select create_date  from goods g WHERE timestampdiff(YEAR,create_date,DATE_SUB(CURDATE(),INTERVAL 10 year)) >=1 


-- 一些时间函数
# 当前时间增加8个小时
SELECT  ADDTIME(NOW(),'08:00:00')
select timestamp (NOW(),'08:00:00')

# 7天之后的日期
select DATE_ADD(NOW(),INTERVAL 7 day)
# 7天之前的日志
select DATE_SUB(NOW(),INTERVAL 7 day) 
select DATE_ADD(NOW(),INTERVAL -7 day) 

# 1个小时20分钟后
select DATE_ADD(NOW(),INTERVAL '01:20' HOUR_MINUTE) 

# 3天零3小时之后
select DATE_ADD(now(), INTERVAL '3 3' DAY_HOUR) 

-- 计算月初和月末

# 最后一天
select last_day(NOW())

# 最开始的一天
select DATE_SUB(NOW(),INTERVAL dayofmonth(NOW())-1 day) 


-- 本月月末之前要发布的文章
-- 如果数据量大，建议先在后端语言中计算出日期，sql中直接比较更快
select * FROM article a 
WHERE publish_time <= last_day(NOW())
AND  publish_time >= DATE_SUB(NOW(), INTERVAL  DAYOFMONTH(NOW())-1 DAY) 

# 取三个月之前从1号开始算起待发布的文章

SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 3 month),"%Y-%m-01") 

select * from article a 
WHERE publish_time >= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 3 month),"%Y-%m-01")
AND  publish_time  <= NOW() 

# 得到上个月的最后一天
SELECT last_day(DATE_SUB(NOW(),INTERVAL 1 month)) 

# 得到下个月的第一天
select DATE_ADD(last_day(NOW()),INTERVAL 1 day) 


-- 日期对星期的控制
dayofweek 1-7 星期日为1 
weekday 0-6   星期一为0


# 查询当前星期的星期二是哪一天
# dayofweek
1    2    3    4    5    6    7 
日    一    二    三    四    五    六

select DATE_ADD(NOW(),INTERVAL 3 - dayofweek(NOW()) day)

# weekday
0    1    2    3    4    5    6
一    二    三    四    五    六    日

select DATE_ADD(NOW(), INTERVAL 1 - WEEKDAY(NOW()) day) 

# 三个礼拜前的礼拜二 ，用本周二减掉21天
select DATE_SUB(DATE_ADD(NOW(), INTERVAL 1 - WEEKDAY(NOW()) day), INTERVAL 21 day) 

# 上周一的日期
# 上周日期
set @beforeWeek = DATE_SUB(NOW(),INTERVAL 7 DAY)
SELECT @beforeWeek

# 把beforeWeek看作今天，来求这周一的日期
select DATE_ADD(@beforeWeek, INTERVAL 0- WEEKDAY(@beforeWeek) day) 


-- 找出这个月迟到的考勤数据 ，8点30分以后算迟到
create table attendance (id int primary key auto_increment , person_id int, create_at date)

alter table attendance modify create_at datetime

select * from  attendance a  WHERE  time(create_at) > '08:30:00'
AND create_at >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW())-1 DAY),'%Y-%m-%d 00:00:00') 

-- 本月迟到次数超过2次
select person_id , COUNT(id) as total  from  attendance a  WHERE  time(create_at) > '08:30:00'
AND create_at >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW())-1 DAY),'%Y-%m-%d 00:00:00') 
GROUP BY person_id 
HAVING  total >= 2 # 对分组进行筛选

-- 本周考勤迟到数据
select person_id ,COUNT(id) as total from  attendance a  WHERE  time(create_at) > '08:30:00'
AND date(create_at) >= date(DATE_ADD(NOW(),INTERVAL 0-WEEKDAY(NOW()) DAY)) 
GROUP by person_id 
ORDER by total DESC 

-- 上周考勤迟到数据
set @beforeWeek = date_sub(NOW(),INTERVAL 1 week)
SELECT  @beforeWeek
# 上周一到周五
select * FROM attendance a 
WHERE date(create_at) >= date(DATE_ADD(@beforeWeek,INTERVAL  0-WEEKDAY(@beforeWeek) DAY))
AND date (create_at) <= date(DATE_ADD(@beforeWeek,INTERVAL 4-WEEKDAY(@beforeWeek) DAY))
AND time(create_at) > '08:30:00'