How Can I Generate a List of Dates in MySQL Between Two Given Dates?

Generate a list of dates between two dates in MySQL

MySQL provides powerful functions that can efficiently generate a date sequence within a specified date range to retrieve all dates within the range.

MySQL Stored Procedure

You can use a stored procedure to create a temporary table named time_intervals that contains the desired date sequence:

<code class="language-sql">CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
  -- ... (存储过程代码，与上面答案中提供的代码相同)
END;</code>

Examples of usage

To generate a list of dates for each day between '2009-01-01' and '2009-01-13', you can use the following command:

<code class="language-sql">CALL make_intervals('2009-01-01', '2009-01-13', 1, 'DAY');

SELECT * FROM time_intervals;</code>

Output results

<code>interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
2009-01-10 00:00:00 2009-01-10 23:59:59 
2009-01-11 00:00:00 2009-01-11 23:59:59 
2009-01-12 00:00:00 2009-01-12 23:59:59 
2009-01-13 00:00:00 2009-01-13 23:59:59</code>

The above is the detailed content of How Can I Generate a List of Dates in MySQL Between Two Given Dates?. For more information, please follow other related articles on the PHP Chinese website!