How can I use mysql to determine the Sundays for the last year? I have a table where I'm currently aggregating ~200 rows per day of stats.  These stats include things like # of users, how many people using X feature, etc.

What I'd like to do is query this table so summarize the data in this way:


Daily over the past X days
Weekly over the past year - Take the stats from each Sunday and pulling the current day (assuming today isn't Sunday)
Monthly over the past X years - Take the stats from the last day of each month and pulling the current day (assuming today isn't the last day of the month)


Columns:


date is the day the data was aggregated
value is the numeric value I need to show (e.g. - # of users)


I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available.  How can I use mysql to determine the Sundays for the last year?

Unfortunately I can't provide an example of what I have working at the moment, I'm still investigating potential solutions.  Most queries I come across are querying records by dates, not generating the dates themselves.

My table structure:

CREATE TABLE `daily_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stat` int(11) NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
You can use a Calendar table for this and other purposes. A calendar table is always useful.

I've set up a minimal example, but usually a Calendar table has more detailed fields like, hours, minutes, ect.

CREATE TABLE Calendar (
    cDate datetime, 
    cDay int, 
    cDayOfWeek int, 
    cDayName varchar(20)
);

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart &lt;= dateEnd DO
    INSERT INTO Calendar (cDate, cDayOfWeek, cDayName) 
    VALUES (dateStart, DAYOFWEEK(dateStart), DAYNAME(dateStart));
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;

CALL filldates('2018-01-01','2018-12-31');



  I want empty records/dates to be filled with zeros so I need to use mysql to generate the dates and then join a matching record where available. How can I use mysql to determine the Sundays for the last year?


You can use the calendar table to build this empty table in this way:

INSERT INTO daily_stats (`date`, `stat`, `value`, `created_at`)
SELECT
    cDate, 0, 0, now()
FROM 
    Calendar 
WHERE 
    cDayOfWeek = 7;


NOTE: cDayOfWeek could be different depending on first day of week in your system.

SELECT * FROM daily_stats LIMIT 10;





SELECT * FROM daily_stats LIMIT 10;



id | date       | stat | value | created_at          | updated_at
-: | :--------- | ---: | ----: | :------------------ | :---------
 1 | 2018-01-06 |    0 |     0 | 2019-01-19 18:16:49 | null      
 2 | 2018-01-13 |    0 |     0 | 2019-01-19 18:16:49 | null      
 3 | 2018-01-20 |    0 |     0 | 2019-01-19 18:16:49 | null      
 4 | 2018-01-27 |    0 |     0 | 2019-01-19 18:16:49 | null      
 5 | 2018-02-03 |    0 |     0 | 2019-01-19 18:16:49 | null      
 6 | 2018-02-10 |    0 |     0 | 2019-01-19 18:16:49 | null      
 7 | 2018-02-17 |    0 |     0 | 2019-01-19 18:16:49 | null      
 8 | 2018-02-24 |    0 |     0 | 2019-01-19 18:16:49 | null      
 9 | 2018-03-03 |    0 |     0 | 2019-01-19 18:16:49 | null      
10 | 2018-03-10 |    0 |     0 | 2019-01-19 18:16:49 | null      



db&lt;>fiddle here