How to Generate a List of Dates Between Two Dates in MySQL?

Generate a list of dates between two dates

To get a list of dates between two given dates, consider using a stored procedure like make_intervals to create a temporary table time_intervals filled with the required intervals. You can then join the data table with the time_intervals table to get the desired output.

make_intervals Stored Procedure (MySQL)

<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
    -- 根据单位和区间计算下一个区间日期
    select
      case unitval
        when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
        when 'SECOND' then timestampadd(SECOND, intval, thisDate)
        when 'MINUTE' then timestampadd(MINUTE, intval, thisDate)
        when 'HOUR' then timestampadd(HOUR, intval, thisDate)
        when 'DAY' then timestampadd(DAY, intval, thisDate)
        when 'WEEK' then timestampadd(WEEK, intval, thisDate)
        when 'MONTH' then timestampadd(MONTH, intval, thisDate)
        when 'QUARTER' then timestampadd(QUARTER, intval, thisDate)
        when 'YEAR' then timestampadd(YEAR, intval, thisDate)
      end into nextDate;

    -- 将当前区间插入到临时表中
    insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
    set thisDate = nextDate;

  -- 继续直到当前日期达到或超过结束日期
  until thisDate >= enddate
  end repeat;
END;</code>

Usage examples

For example, to create a list of dates between January 1, 2009 and January 13, 2009, you would call the make_intervals stored procedure as follows:

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

This will populate the time_intervals table with intervals representing each day in the specified date range. You can then join the data table with the time_intervals table to summarize the data based on these intervals.

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