Mysql – How to use thesql to determine the Sundays for the last year

datedate mathMySQL

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

Best Answer

I don't have enough reputation to comment, however I think this is what you're looking for. Check out "Date Series Generation" in this document. https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-date-series

Common Table Expressions, which enable Date Series Generation, are available as of MySQL 8.0, or Mariadb 10.2.2. If you're on an earlier version and can't upgrade, I think @McNets answer is the way to go.

Related Question