How to Generate a Date Range in MySQL Without a Calendar Table?

Cleverly generate MySQL date range without calendar table

PostgreSQL’s generate_series() function can conveniently generate date ranges. However, MySQL has no direct equivalent function. This article provides a solution to help you implement similar functionality in MySQL.

Challenge

It is often necessary to perform queries that involve joining data with a complete date range. However, this task can be tedious if your database does not have a specific calendar table.

MySQL Solution

Create a date range in MySQL using the provided example table:

SELECT 
    DATE_FORMAT(
        ADDDATE('2011-1-1', @num := @num + 1), 
        '%Y-%m-%d'
    ) AS 日期
FROM 
    任意表,    
    (SELECT @num := -1) AS num
LIMIT 
    365;

Description

This query generates a date range from '2011-01-01' to '2011-12-31' using the following steps:

1. 任意表 is used as a reference to create a virtual line number range from -1 to 364 (-1 to 365 for leap years).
The
2. ADDDATE() function adds the appropriate number of days to the starting date.
3. DATE_FORMAT() The function formats the generated date into the required 'YYYY-MM-DD' format.

Integration

This query can be used as a subquery within the main query to join with your original table. For example:

SELECT 
    t.日期, 
    COALESCE(d.qty, 0) AS qty
FROM 
    (
        SELECT 
            DATE_FORMAT(
                ADDDATE('2011-1-1', @num := @num + 1), 
                '%Y-%m-%d'
            ) AS 日期
        FROM 
            任意表,    
            (SELECT @num := -1) AS num
        LIMIT 
            365
    ) AS t
LEFT JOIN 
    原始表 AS d 
ON 
    t.日期 = d.日期;

This query will return the desired results, where rows with missing dates will be filled with '0' qty values.

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