How to Generate a Date Series in MySQL?

Generate date sequence in MySQL

Question:

How to create a date series in MySQL, similar to the generate_series() function in PostgreSQL?

Answer:

MySQL does not have a direct equivalent to generate_series(), but we can achieve similar results by following these steps:

1. Create a temporary table containing consecutive numbers.
2. Use the adddate() function to add the corresponding date interval to each number.
3. Use the date_format() function to format the generated date so that it conforms to the desired format.

The following is a sample query to create a date range from January 1, 2011 to December 31, 2011:

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

In this query:

• 任意表 is a temporary table containing one or more rows (any table can be used, as only the number of rows is used).
• @num := @num 1 Creates a sequence of consecutive numbers starting from 0.
• ADDDATE('2011-01-01', @num := @num 1) Add the corresponding date interval to each number.
• DATE_FORMAT() Format the generated date into the desired format.
• LIMIT 365 Specifies the number of dates to generate (365 for non-leap years).

This method allows us to generate date sequences in MySQL without the need for a separate calendar table. Note that 任意表 can be replaced with any existing table, or used to create temporary data rows. This is a workaround because MySQL itself does not provide direct functions like PostgreSQL SELECT 1 UNION ALL SELECT 1 .... generate_series

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