How to Generate Date Ranges in MySQL Using Date Arithmetic and Subqueries?

Use MySQL date operations and subqueries to generate date ranges

In SQL, retrieving a list of dates within a specified range can be achieved by using a combination of date operations and subqueries. Here's how to accomplish this:

In order to generate a list of dates between two dates, we can use the subquery method. A nested set of subqueries, each generating a sequence of numbers (from 0 to 9) that are combined by addition to form the desired date value.

The following query demonstrates this technique:

<code class="language-sql">select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'</code>

This query will generate a list of dates from '2012-02-10' to '2012-02-15' (inclusive). The results are as follows:

<code>date
----------
2012-02-10
2012-02-11
2012-02-12
2012-02-13
2012-02-14
2012-02-15</code>

This method allows generating date ranges spanning nearly 300 years in the future or past.

The above is the detailed content of How to Generate Date Ranges in MySQL Using Date Arithmetic and Subqueries?. For more information, please follow other related articles on the PHP Chinese website!