How to Generate Date Ranges in MySQL Without `generate_series()`?

MySQL's Date Range Generation: A Practical Approach

MySQL lacks a direct equivalent to PostgreSQL's generate_series() function. However, we can cleverly generate date ranges using SQL and variables. This method is particularly useful when you need to create date ranges without a pre-existing calendar table.

Let's illustrate with an example. Imagine a sales table:

date	qty
2011-01-01	3
2011-01-01	4
2011-04-01	2
2011-06-01	5

Our goal is to expand this to show daily sales for the entire year 2011:

date	qty
2011-01-01	7
2011-01-02	0
2011-01-03	0
2011-04-01	2
...and so on...

Here's how we achieve this:

<code class="language-sql">SELECT 
    DATE_FORMAT(ADDDATE('2011-01-01', @num := @num + 1), '%Y-%m-%d') AS date
FROM 
    any_table, 
    (SELECT @num := -1) AS num
LIMIT 
    365;</code>

This query uses any_table (any existing table will do) as a placeholder to ensure the query runs. The @num variable, initialized to -1, increments with each row, generating consecutive dates starting from '2011-01-01'. LIMIT 365 restricts the output to a non-leap year; use LIMIT 366 for leap years. The DATE_FORMAT function ensures consistent date formatting.

This technique provides a flexible and efficient solution for generating date ranges within MySQL, simplifying date-based queries and analysis.

The above is the detailed content of How to Generate Date Ranges in MySQL Without `generate_series()`?. For more information, please follow other related articles on the PHP Chinese website!