mysql獲取一個時間段中所有日期或者月份
1：mysql獲取時間段所有月份
select DATE_FORMAT(date_add('2020-01-20 00:00:00', interval row MONTH),'%Y-%m') date from
(
SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(SELECT @row:=-1) r
) se
where DATE_FORMAT(date_add('2020-01-20 00:00:00', interval row MONTH),'%Y-%m') = DATE_FORMAT('2020-04-02 00:00:00','%Y-%m')
2：mysql獲取時間段所有日期
select date_add('2020-01-20 00:00:00', interval row DAY) date from
(
SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(SELECT @row:=-1) r
) se
where date_add('2020-01-20 00:00:00', interval row DAY) = '2020-03-02 00:00:00'
備注：
這段代碼表示數據條數限制，寫兩次查詢的日期最多顯示100條，寫三次查詢日期最多顯示1000次，以此類推，根據你自己的需求決定
下面是設置最多顯示條數10000寫法
希望能幫助到你，萌新在線求帶！！！
下面是其他網友的補充大家可以參考一下
1、不使用存儲過程，不使用臨時表，不使用循環在Mysql中獲取一個時間段的全部日期
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2017-11-10' and '2017-11-15'
輸出如下
Date
----------
2017-11-15
2017-11-14
2017-11-13
2017-11-12
2017-11-11
2017-11-10
2、mysql獲取兩個日期內的所有日期列表
select @num:=@num+1,date_format(adddate('2015-09-01', INTERVAL @num DAY),'%Y-%m-%d') as date
from btc_user,(select @num:=0) t where adddate('2015-09-01', INTERVAL @num DAY) = date_format(curdate(),'%Y-%m-%d')
order by date;
此方法優點就是不需要創建存儲過程或者是日歷表，缺點就是你必須要有一個表，它的數據條數大到足夠支撐你要查詢的天數
3、mysql獲取給定時間段內的所有日期列表(存儲過程)
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_custom (
`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_custom VALUES (DATE(s_date)) ;
SET s_date = s_date + INTERVAL 1 DAY ;
END WHILE ;
END$$
DELIMITER ;
-- 生成數據到calendar_custom表2009-01-01~2029-01-01之間的所有日期數據
CALL create_calendar (‘2009-01-01‘, ‘2029-01-01‘);
DELIMITER $$
DROP PROCEDURE IF EXISTS create_calendar $$
CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)
BEGIN
-- 生成一個日歷表
SET @createSql = ‘truncate TABLE calendar_custom‘;
prepare stmt from @createSql;
execute stmt;
WHILE s_date = e_date DO
INSERT IGNORE INTO calendar_custom VALUES (DATE(s_date)) ;
SET s_date = s_date + INTERVAL 1 DAY ;
END WHILE ;
END$$
DELIMITER ;
-- 生成數據到calendar_custom表2009-01-01~2029-01-01之間的所有日期數據
CALL create_calendar (‘2009-01-02‘, ‘2009-01-07‘);
到此這篇關于mysql獲取指定時間段中所有日期或月份的語句(不設存儲過程，不加表)的文章就介紹到這了,更多相關mysql獲取指定時間段中的日期與月份內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 解析MySQL中存儲時間日期類型的選擇問題
- MySQL存儲時間類型選擇的問題講解
- 如何選擇合適的MySQL日期時間類型來存儲你的時間
- 解決MySQL存儲時間出現不一致的問題
- MySQL中存儲時間的最佳實踐指南
