mysql - Database structure for holding statistics by day, week, month, year

Question

I have to collect statisctics by days, weeks, months and years of user activity for a site. I am the DB design stage and I wanted to do this stage properly since it will make my coding life easier.

What I have to do is just simply increment the values in the fields by 1 in the DB each time an activity happens. So then I can pull up the date by each day, each week, each month and year. How should my DB be structured? Apologies if this is a simple question for most. It would also be great if this structure could be extendable so that it can be broken down by other categories.

The bit am having trouble with is each month is made up of more days and these days change each calender year.

Thanks all for any help or direction.

Other info: Linux Machine, making use of PHP and MySQL

question from:https://stackoverflow.com/questions/65947482/how-to-effectively-save-last-7-days-statistics-in-sql-database

Answer 1

Instead of updating counts per day, week etc. just INSERT a row into a table each time an activity happens like this:

insert into activities (activity_date, activity_info) 
values (CURRENT_TIMESTAMP, 'whatever');

Now your reports are very simple like:

select count(*) from activities
where activity_date between '2008-01-01' and '2008-01-07';

or

select YEARWEEK(`activity_date`) as theweek, count(*)
group by theweek