您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“mysql怎么獲取指定時(shí)間段中所有日期或月份”,感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷,實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“mysql怎么獲取指定時(shí)間段中所有日期或月份”吧!
mysql獲取一個(gè)時(shí)間段中所有日期或者月份
1:mysql獲取時(shí)間段所有月份
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獲取時(shí)間段所有日期
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'
備注:
這段代碼表示數(shù)據(jù)條數(shù)限制,寫兩次查詢的日期最多顯示100條,寫三次查詢?nèi)掌谧疃囡@示1000次,以此類推,根據(jù)你自己的需求決定
下面是設(shè)置最多顯示條數(shù)10000寫法
1、不使用存儲(chǔ)過(guò)程,不使用臨時(shí)表,不使用循環(huán)在Mysql中獲取一個(gè)時(shí)間段的全部日期
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獲取兩個(gè)日期內(nèi)的所有日期列表
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;
此方法優(yōu)點(diǎn)就是不需要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程或者是日歷表,缺點(diǎn)就是你必須要有一個(gè)表,它的數(shù)據(jù)條數(shù)大到足夠支撐你要查詢的天數(shù)
3、mysql獲取給定時(shí)間段內(nèi)的所有日期列表(存儲(chǔ)過(guò)程)
DELIMITER $$ DROP PROCEDURE IF EXISTS create_calendar $$ CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE) BEGIN -- 生成一個(gè)日歷表 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 ; -- 生成數(shù)據(jù)到calendar_custom表2009-01-01~2029-01-01之間的所有日期數(shù)據(jù) 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 -- 生成一個(gè)日歷表 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 ; -- 生成數(shù)據(jù)到calendar_custom表2009-01-01~2029-01-01之間的所有日期數(shù)據(jù) CALL create_calendar (‘2009-01-02‘, ‘2009-01-07‘);
到此,相信大家對(duì)“mysql怎么獲取指定時(shí)間段中所有日期或月份”有了更深的了解,不妨來(lái)實(shí)際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。