Mysql生成任意指定两时间范围内的日期列表

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/SunFlowerXT/article/details/89468116

前言:工作当中有一个场景是数据库存了一些数据,但是日期是零碎的,只有当用户进行了相应日期的操作才会有对应记录的生成。在做所有的数据统计可视化的时候,需要看到的是每一天的日期,此时就需要提供一段时间范围内的所有日期。

在网上查找资料,看到的有三种方法,总结如下:

一、创建存储过程(即创建一个临时的日历表。我是这样理解的),

例一:

以下是我从别人的博客抄过来的例子,这个例子是将日期范围写在创建的逻辑当中,感兴趣的伙伴可以试一下执行,当执行了调用的语句可以查看自己数据库中是否多了一张表。

例二:

这是另外一个创建存储过程,不同于以上的例子,这个可以直接在调用的时候给时间范围。对于日期范围需要和用户交互,需要灵活更改的,这个方法更加适用

DELIMITER $$
DROP PROCEDURE IF EXISTS create_calendar $$
CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)
BEGIN
 
	SET @createSql = 'CREATE TABLE IF NOT EXISTS calendar (
                      `date` date NOT NULL,
		       UNIQUE KEY `unique_date` (`date`) USING BTREE
                   )ENGINE=InnoDB DEFAULT CHARSET=utf8'; 
	prepare stmt from @createSql; 
	execute stmt; 
 
	WHILE s_date <= e_date DO
		INSERT IGNORE INTO calendar VALUES (DATE(s_date)) ;
		SET s_date = s_date + INTERVAL 1 DAY ;
	END WHILE ; 
 
END$$
DELIMITER ;
 
CALL create_calendar ('2018-03-01', '2018-12-30');

总结:使用存储过程的优点网上随便一查就能查到,以上这两种方式都能生成一张临时表,里面存放着你自己指定的时间范围内的所有日期。根据自己业务需求,可以选取其中一种方法,生成临时表,再与你的数据进行各种操作得出某段时间范围内日期齐全的数据。


二、(变量控制)指定数据条数,生成连续的数字或日期

例一:

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc DAY), '%Y-%m-%d') as date
FROM ( 
			SELECT @xi:=@xi+1 as xc from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
			(SELECT @xi:=0) xc0 
) xcxc

 

结果如下所示:

 

 

以上代码的作用就是生成当日前25(包括当月)个日的日期。具体解释如下

查询日期的详解

 

 

总结:在如上的例子当中,涉及到的知识点是变量,DATE_SUB(),DATE_FORMAT().使用以上方法的好处就是不用创建存储过程,也不涉及到任何表。缺点就是数据的条数控制并不灵活,不能和用户之间形成互动,即不能自定义日期区间,只能控制数据条数。

三、(利用现有的表做操作)

要求是这个表中的数据足够庞大,好处是不涉及存储过程,不涉及变量,且能自定义日期区间。

select 
@num:=@num+1,
date_format(adddate(DATE_SUB('2019-01-17',INTERVAL 1 DAY), INTERVAL @num DAY),'%Y-%m-%d') as date
from order_info,(select @num:=0) t 
where adddate('2019-01-17', INTERVAL @num DAY) <= DATE_FORMAT(CURDATE(),'%Y-%m-%d')
order by date;

执行的结果是:

这个是直接把开始时间写成一个固定值的,当然,这个开始时间也类似于一个参数,是可传的,比如我不知道当前表中最开始的时间,那么,我们就只能通过查询出来之后再从当天开始:

select 
@num:=@num+1,
date_format(adddate(DATE_SUB((SELECT create_time FROM order_info ORDER BY create_time LIMIT 0,1),INTERVAL 1 DAY), INTERVAL @num DAY),'%Y-%m-%d') as date
from order_info,(select @num:=0) t 
where adddate((SELECT create_time FROM order_info ORDER BY create_time LIMIT 0,1), INTERVAL @num DAY) <= (SELECT MAX(create_time) FROM order_info WHERE is_delete =0)
order by date;

这里的起止时间,都是查询当前表中最大和最小的日期,结果如果:

 

 

这些sql的具体解释,如下:

 

总结:这个是我选择的方法,因为业务需要,我不能用创建存储过程的方法,我主要是做的数据的统计,一些没有产生数据的日期,也需要显示出来,再加上还需要统计别的一些信息,所以我选择的最后这种利用现有的表来查询,再去做自己需要的一些查询。

写到这里,也就差不多,以上的几种方法,也是我百度来的,可以根据自己的具体需求选择具体的方法。

 

 

 

展开阅读全文

没有更多推荐了,返回首页