Generate Date Series in popular Databases

In this post I want to answer to frequently asked question: How I can generate date series between to particular dates?

Generating a date series between two particular dates can be done using different methods depending on the relational database management system (RDBMS) you are using. I'll provide examples for a few popular RDBMS systems: MySQL, PostgreSQL, and Microsoft SQL Server.

Please note that the syntax might slightly differ based on the specific version of the RDBMS you're using, so you should consult the documentation for your specific version if you encounter any issues.

MySQL

Legacy MySQL (5.7.*)
The old MySQL doesn't have built-in functions to generate a date series, so you might need to use a temporary table or a numbers table. Here's an example using a numbers table approach:

CREATE TEMPORARY TABLE Numbers (n INT);
-- Insert numbers up to the desired range
INSERT INTO Numbers VALUES (0), (1), (2), ...;  

SELECT 
    DATE_ADD('start_date', INTERVAL n DAY) AS generated_date
FROM Numbers
WHERE 
    DATE_ADD('start_date', INTERVAL n DAY) <= 'end_date';

Just replace 'start_date' and 'end_date' with your desired start and end dates and try it on SQLize.online.

In Modern MySQL 8.0.*, you can use a Common Table Expression (CTE) to generate a date series between two particular dates. Here's how you can do it:

SET @start_date = '2022-01-01';
SET @end_date = '2022-01-31';

WITH RECURSIVE DateSeries AS (
    SELECT @start_date AS generated_date
    UNION ALL
    SELECT DATE_ADD(generated_date, INTERVAL 1 DAY)
    FROM DateSeries
    WHERE generated_date < @end_date
)
SELECT generated_date
FROM DateSeries;

Explanation:

1. The WITH RECURSIVE clause defines the CTE named DateSeries.
2. In the initial SELECT statement within the CTE, we set the anchor value to the start date.
3. In the recursive SELECT statement, we use the DATE_ADD function to increment the date by one day for each iteration.
4. The WHERE clause in the recursive SELECT statement ensures that the recursion continues until the generated date is less than the end date.
5. Finally, the outer SELECT statement selects all the generated dates from the CTE.

Remember that recursive queries can be resource-intensive, so use them cautiously and only when necessary. Try the query here

PostgreSQL

PostgreSQL has the generate_series function that makes this task easy:

SELECT generate_series('2022-01-01'::date, '2022-01-31'::date, '1 day') AS generated_date;

Replace 'start_date' and 'end_date' with your desired start and end dates.

Microsoft SQL Server

SQL Server also has a similar approach using the sys.dates system table and the DATEADD function:

DECLARE @start_date DATE = '2022-01-01'
DECLARE @end_date DATE = '2022-01-31'

SELECT TOP 
    (DATEDIFF(day, @start_date, @end_date) + 1)
    generated_date = DATEADD(day, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @start_date)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

Since SQL Server 2022 where implemented GENERATE_SERIES function you can use it for generate dates series too in next way:

SELECT 
    DATEADD(day, value, '2022-01-01') AS Date
FROM GENERATE_SERIES(0, DATEDIFF(day, '2022-01-01', '2022-01-31'))

Oracle

SELECT DATE '2022-01-01' + LEVEL - 1 AS generate_series
FROM dual
CONNECT BY LEVEL <= DATE '2022-01-31' - DATE '2022-01-01' + 1

Another cool method:

SELECT TRUNC (DATE '2023-01-01' + ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < 31

If you know more methods to get date series in different RDBMS, please post in comments