MySQL statement to get all dates or months in a specified time period (without setting stored procedures or adding tables)

mysql gets all dates or months in a time period

1: mysql gets all months in the time period

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
 )
 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 gets all dates in a time period

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
 )
 where date_add('2020-01-20 00:00:00', interval row DAY) <= '2020-03-02 00:00:00'

Remark:

This code indicates the limit on the number of data entries. If you write two queries, the maximum number of displayed data entries is 100. If you write three queries, the maximum number of displayed data entries is 1000. And so on. You can decide according to your needs.

The following is how to set the maximum number of displayed items to 10,000

I hope this helps. Newbie online looking for guidance! ! !

The following are the supplements from other netizens for your reference

1. Get all dates in a time period in MySQL without using stored procedures, temporary tables, or loops

select a.Date 
from (
    select curdate() - INTERVAL (aa + (10 * ba) + (100 * ca)) 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
)
where a.Date between '2017-11-10' and '2017-11-15'

The output is as follows

Date
----------
2017-11-15
2017-11-14
2017-11-13
2017-11-12
2017-11-11
2017-11-10

2. MySQL gets a list of all dates within two dates

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;

The advantage of this method is that you don't need to create a stored procedure or calendar table. The disadvantage is that you must have a table with enough data to support the number of days you want to query.

3. MySQL gets a list of all dates within a given time period (stored procedure)

DELIMITER $$
DROP PROCEDURE IF EXISTS create_calendar $$
CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)
BEGIN
-- Generate a calendar table 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 ;
-- Generate data to the calendar_custom table for all dates between 2009-01-01 and 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
-- Generate a calendar table 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 ;
-- Generate data to the calendar_custom table for all dates between 2009-01-01 and 2029-01-01 CALL create_calendar ('2009-01-02', '2009-01-07');

This is the end of this article about MySQL statements for getting all dates or months in a specified time period (without setting up stored procedures or adding tables). For more information about MySQL statements for getting dates and months in a specified time period, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

How to use MySQL DATEDIFF function to get the time interval between two dates
Mysql timeline data to obtain the first three data of the same day
Detailed explanation of MySQL to obtain statistical data for each day and each hour of a certain period of time
MySQL example of getting today and yesterday's 0:00 timestamp
mysql gets yesterday's date, today's date, tomorrow's date, and the time of the previous hour and the next hour
mysql obtains statistical data within a specified time period
How to get time in mysql

<<: The url value of the src or css background image is the base64 encoded code

>>: Detailed method of using goaccess to analyze nginx logs