When building dashboards or analytics features, it's common to visualize metrics like daily user signups, page views, or logins over a date range. A key requirement is that the result must include every day in the interval, even if no activity occurred on that day—those antries should show a count of zero.

Sample Schema

Consider a user table with the following structure:

CREATE TABLE `user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `username` VARCHAR(20) NOT NULL COMMENT 'Username',
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `phone` VARCHAR(20) NULL DEFAULT NULL,
  `password` VARCHAR(128) NULL DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login` DATETIME(6) NULL DEFAULT NULL COMMENT 'Last login timestamp'
);

Solution 1: Recursive CTE (MySQL 8.0+)

Using a recursive Common Table Expression (CTE), we can generate a complete sequence of dates between two points and left-join it with actual data:

WITH RECURSIVE date_range AS (
  SELECT DATE(MIN(created_at)) AS dt FROM user
  UNION ALL
  SELECT dt + INTERVAL 1 DAY
  FROM date_range
  WHERE dt < CURDATE()
)
SELECT
  dr.dt AS registration_date,
  COUNT(u.id) AS daily_registrations
FROM date_range dr
LEFT JOIN user u
  ON u.created_at >= dr.dt
 AND u.created_at < dr.dt + INTERVAL 1 DAY
GROUP BY dr.dt
ORDER BY dr.dt;

This avoids applying DATE() on the indexed created_at column, preserving index usage. For high-frequancy queries over large datasets, consider adding a dedicated created_date DATE column with an index to enable equality joins.

Solution 2: Calendar Helper Table

Create a permanent calendar dimension table once:

CREATE TABLE dim_calendar (day DATE PRIMARY KEY);

INSERT INTO dim_calendar (day)
SELECT DATE_ADD('2020-01-01', INTERVAL seq DAY)
FROM (
  SELECT a.N + b.N*10 + c.N*100 + d.N*1000 AS seq
  FROM (SELECT 0 N UNION SELECT 1 UNION ... UNION SELECT 9) a,
       (SELECT 0 N UNION SELECT 1 UNION ... UNION SELECT 9) b,
       (SELECT 0 N UNION SELECT 1 UNION ... UNION SELECT 9) c,
       (SELECT 0 N UNION SELECT 1 UNION ... UNION SELECT 9) d
) nums
WHERE seq <= 4018; -- Covers ~11 years

Then query using this table:

SELECT
  c.day AS registration_date,
  COUNT(u.id) AS daily_registrations
FROM dim_calendar c
LEFT JOIN user u
  ON u.created_at >= c.day
 AND u.created_at < c.day + INTERVAL 1 DAY
WHERE c.day BETWEEN DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND CURDATE()
GROUP BY c.day
ORDER BY c.day;

Solution 3: Numbers Table Approach

For shorter ranges, use a numbers table:

CREATE TABLE numbers (n TINYINT PRIMARY KEY);
INSERT INTO numbers (n)
SELECT units.n + tens.n * 10
FROM (SELECT 0 n UNION ... UNION SELECT 9) units,
     (SELECT 0 n UNION ... UNION SELECT 9) tens;

Query example for a specific range:

SET @start = '2024-03-01';
SET @end   = '2024-03-31';

SELECT
  DATE_ADD(@start, INTERVAL n DAY) AS registration_date,
  COUNT(u.id) AS daily_registrations
FROM numbers
LEFT JOIN user u
  ON u.created_at >= DATE_ADD(@start, INTERVAL n DAY)
 AND u.created_at < DATE_ADD(@start, INTERVAL n + 1 DAY)
WHERE n <= DATEDIFF(@end, @start)
GROUP BY n
ORDER BY registration_date;

ECharts Integration

The backend should return a dense array of daily records, including zeros. In JavaScript, format the data for ECharts using either explicit arrays or the dataset API:

// Option 1: Manual split
const rawData = [
  { date: '2025-07-01', count: 10 },
  { date: '2025-07-02', count: 0 },
  // ...
];
const [dates, counts] = rawData.reduce(
  ([d, c], item) => [ [...d, item.date], [...c, item.count] ],
  [[], []]
);
option = {
  xAxis: { type: 'category', data: dates },
  yAxis: { type: 'value' },
  series: [{ type: 'bar', data: counts }]
};

// Option 2: Dataset (preferred)
option = {
  dataset: {
    dimensions: ['date', 'count'],
    source: rawData
  },
  xAxis: { type: 'category' },
  yAxis: { type: 'value' },
  series: [{ type: 'bar' }]
};