How to save daily / weekly / monthly data ?

Discussion in 'Programming' started by AKnogood, Jan 14, 2013.

  1. #1
    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?
     
    AKnogood, Jan 14, 2013 IP
  2. goliath

    goliath Active Member

    Messages:
    308
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    60
    #2
    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.
     
    goliath, Jan 15, 2013 IP