I have an arcade website where each game has a "Daily Plays" variable in a SQL database and at the end of every day I add this value to a "All Time Plays" variable and reset the "Daily Plays" variable. I would now like to record the "Weekly Plays" (last 7 days) and "Monthly Plays" (last 30 days) for those games. What would be the best and most efficient way to do that?
hmmm not sure if this is the most efficient but it won't require much. You actually add four fields to your table, monthly_start, weekly_start, monthly_play and weekly_play. You save a timestamp in the fields named "start". each day when your script updates the all time plays variable, it also checks the timestamps on the weekly_start and monthly_start fields. If it has been less than seven days since your timestamp in weekly_start field you increment your weekly_play field. If it has been more than seven days, you reset it. Repeat for month on a 30 day cycle, or use actual calendar months if you like. That should get you what you like with reasonable efficiency.