Get the data of the last week from (Monday to Sunday)-CodePudding

I would like to get the data from the last week. A week is Monday to Sunday.

I made this query :

    sql = 'SELECT COUNT(*) ' \
          'FROM panelname ' \
          'WHERE year(date) = year(now()) ' \
          'AND date BETWEEN date_sub(now(),INTERVAL 1 WEEK) AND now() '
    cursor.execute(sql)
    test = cursor.fetchall()
    print(test)

The problem with AND date BETWEEN date_sub(now(),INTERVAL 1 WEEK) AND now() is that it takes the data from the past 7 days. If I am Tuesday of the current week and I run it, I will get the data from last week's Tuesday up until this week's Tuesday, which is not what I want. It should give the data from last Monday down to last Sunday.

I also precise that it should not use the DATEADD function, because I don't have the admin rights to use it.

For example : I run the query today, it doesn't matter which day we are, and I get the last week of data (Monday to Sunday).

Thank you.

CodePudding user response：

You must first get the most recent Monday and use it as a reference point to calculate the 5 previous weeks:

SELECT COUNT(*)
FROM panelname
WHERE date >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) day - INTERVAL 5 week
AND date < CURDATE() - INTERVAL WEEKDAY(CURDATE()) day

UPDATE

To get the aggregated count per each week you need to group by week:

SELECT WEEKOFYEAR(date),COUNT(*)
FROM panelname
WHERE date >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) day - INTERVAL 5 week
AND date < CURDATE() - INTERVAL WEEKDAY(CURDATE()) day
GROUP BY WEEKOFYEAR(date)