Create a Date Table Using the SQL Editor

A date table is useful for building time-based reports and visualizations, especially when your dataset does not contain continuous dates. Instead of creating a physical table in your database, you can generate a date table directly in the SQL Editor using MariaDB’s built-in sequence tables.

How It Works

MariaDB includes several built-in sequence tables that contain sequential numbers starting from 0. These numbers can be converted into calendar dates by adding them to a starting date.

Common sequence tables include:

• seq_0_to_364
• seq_0_to_999
• seq_0_to_29584

Each row contains a seq value, which represents the number of days to add to your chosen start date.

Generate Dates for One Year

The following query generates one row for every day in the year and includes commonly used calendar attributes such as year, month, quarter, week, and weekday.

SELECT
    d AS Date,
    YEAR(d) AS Year,
    MONTH(d) AS Month,
    MONTHNAME(d) AS MonthName,
    CONCAT('Q', QUARTER(d)) AS Quarter,
    WEEK(d, 1) AS Week,
    DAYNAME(d) AS DayOfWeek,
    DAYOFWEEK(d) IN (1,7) AS IsWeekend
FROM (
    SELECT DATE('2026-01-01') + INTERVAL seq DAY AS d
    FROM seq_0_to_364
) t;

How This Query Works

• seq_0_to_364 generates 365 sequential values.
• Each value is added as a day offset to 2026-01-01.
• The result is one row for every calendar date in the year.
• Additional columns provide useful date attributes for reporting and filtering.

Generate a Custom Date Range

For longer periods, use a larger sequence table and filter the dates you need.

SELECT d AS Date
FROM (
    SELECT DATE('1970-01-01') + INTERVAL seq DAY AS d
    FROM seq_0_to_29584
) t
WHERE d BETWEEN '2026-01-01' AND '2026-12-31';

This approach generates a larger date range and returns only the dates that match the specified period.