How Can I Generate a List of Dates Between Two Dates in MySQL Using a Stored Procedure?

Generate a list of dates between two dates

Getting a list of dates between two dates is a common need in various data processing scenarios. Standard MySQL functions have limited options for this functionality.

An efficient way to generate a list of dates is to utilize a stored procedure to populate a temporary table. Here is a detailed process available:

<code class="language-sql">CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
    DECLARE thisDate timestamp;
    DECLARE nextDate timestamp;
    SET thisDate = startdate;

    DROP TEMPORARY TABLE IF EXISTS time_intervals;
    CREATE TEMPORARY TABLE IF NOT EXISTS time_intervals
        (
        interval_start timestamp,
        interval_end timestamp
        );

    REPEAT
        CASE unitval
            WHEN 'MICROSECOND' THEN SET nextDate = TIMESTAMPADD(MICROSECOND, intval, thisDate)
            WHEN 'SECOND' THEN SET nextDate = TIMESTAMPADD(SECOND, intval, thisDate)
            WHEN 'MINUTE' THEN SET nextDate = TIMESTAMPADD(MINUTE, intval, thisDate)
            WHEN 'HOUR' THEN SET nextDate = TIMESTAMPADD(HOUR, intval, thisDate)
            WHEN 'DAY' THEN SET nextDate = TIMESTAMPADD(DAY, intval, thisDate)
            WHEN 'WEEK' THEN SET nextDate = TIMESTAMPADD(WEEK, intval, thisDate)
            WHEN 'MONTH' THEN SET nextDate = TIMESTAMPADD(MONTH, intval, thisDate)
            WHEN 'QUARTER' THEN SET nextDate = TIMESTAMPADD(QUARTER, intval, thisDate)
            WHEN 'YEAR' THEN SET nextDate = TIMESTAMPADD(YEAR, intval, thisDate)
        END;

        INSERT INTO time_intervals SELECT thisDate, TIMESTAMPADD(MICROSECOND, -1, nextDate);
        SET thisDate = nextDate;
    UNTIL thisDate >= enddate
    END REPEAT;

END;</code>

Usage:

1. Call the make_intervals stored procedure using the start date, end date, interval length, and interval unit. For example, to generate the daily interval between '2009-01-01 00:00:00' and '2009-01-10 00:00:00', call:

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

2. Join your data table with the time_intervals table to aggregate data based on date intervals. For example:

<code class="language-sql">SELECT
    aggregate_function(your_data_table.column_name) AS aggregated_value,
    time_intervals.interval_start AS date
FROM your_data_table
LEFT JOIN time_intervals ON (your_data_table.date_column BETWEEN time_intervals.interval_start AND time_intervals.interval_end)
GROUP BY time_intervals.interval_start;</code>

Example output:

<code>+----------------------------+----------+
| aggregated_value | date       |
+----------------------------+----------+
| 值1                  | 2009-01-01 |
| 值2                  | 2009-01-02 |
| 值3                  | 2009-01-03 |
| 值4                  | 2009-01-04 |
| 值5                  | 2009-01-05 |
| 值6                  | 2009-01-06 |
| 值7                  | 2009-01-07 |
| 值8                  | 2009-01-08 |
| 值9                  | 2009-01-09 |
+----------------------------+----------+</code>

This method provides a flexible way to generate a list of dates between two specified dates, enabling convenient and efficient data aggregation based on date intervals.

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