How to Generate a Sequential Date Range in MySQL for Data Visualization?

Create a Sequential Date Range in MySQL for Userbase Growth Graph

To create a sequential date range in MySQL, even for days with no records, you can utilize the following technique:

<br>SELECT DATE_ADD('2003-01-01 00:00:00.000', INTERVAL n5.num<em>10000 n4.num</em>1000 n3.num<em>100 n2.num</em>10 n1.num DAY ) AS date<br>FROM<br>(SELECT 0 AS num 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) n1,<br>(SELECT 0 AS num 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) n2,<br>(SELECT 0 AS num 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) n3,<br>(SELECT 0 AS num 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) n4,<br>(SELECT 0 AS num 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) n5<br>WHERE date >='2011-01-02 00:00:00.000' AND date < NOW()<br>ORDER BY date<br>

The query generates a sequential range of dates by combining the columns n1 to n5, each representing single digits. The total range is determined by the maximum value of each column (e.g., if n3 has a maximum value of 3, the range spans 399 days).

To limit the date range, simply replace the WHERE clause with the desired date interval, such as:

<br>WHERE date >= MIN(your_table_date_field) AND date < NOW()<br>

The above is the detailed content of How to Generate a Sequential Date Range in MySQL for Data Visualization?. For more information, please follow other related articles on the PHP Chinese website!